Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Rolling 52 week high and low

Riccid
6 - Meteoroid

Hi there,
I would like to find the most efficient way, most likely a macro, to calculate the 52 week high and low.
Currently, I am limited to bringing in the data source twice which looks something like:

Transport        client                       Date                   Quote  

airplane           Commercial       01/02/17                 115.60

truck                private                01/02/17                  150.32

 

 

The data I work with is not necessarily of the same frequency e.g. daily, weekly, monthly etc. Therefore the row count approach is not feasible. 

What I did, was to bring the data source in twice, and for each Date by transport and client, I have joined it back to itself so that for each date I have all the dates for that transport type/client. Obviously this creates enormous levels of duplication and processing is rather difficult. 

At this stage, I then introduce a datetimediff calculation between the left date and the duplicated right date ("available date") and then filter on the datetimediff  which should be less than 364 days ("52 weeks") and more than -1. 

I am then left with 52 weeks worth of Date for each date and I can then do a max and min summary for each transport/client. 
The information obtained is then joined back to the initial source. 

This approach worked very well on a test dataset, but it is not processing very well once adapted on a real-life size database.  


Can anyone suggest a better approach please?

 

 

 the ideal output would be:

 

Transport        client                       Date                   Quote               52 week min            52 week high

airplane           Commercial       01/01/17                 112.90                 112.90                         112.90 

truck                private                01/01/17                  120.70              120.70                       120.70

airplane           Commercial       01/02/17                 115.60                  112.90                       115.60

truck                private                01/02/17                  150.32               120.70                        150.32





Regards,

Denise


 

5 REPLIES 5
patrick_digan
17 - Castor
17 - Castor

The attached is using a batch macro that should do the trick. I simulated some data just to test it out. I'm currently just having it grouped by one field. If you needed to group by more than 1 field, you would need to adjust the macro: add a string field to the macro input, Add the field as a group by to the summarize, and add it to the join tool as one of the fields to join on. After you save the macro, you would go back to your workflow and there should be a new field that you can map. While this macro should handle gaps (ie it doesn't care about the frequency of the data), i don't know if the desired output will be ok if you have more than 1 item per day.

 

Let me know what you think!

Riccid
6 - Meteoroid

Hi Patrick,

Took me a bit to get a grip with this one but eventually adapted it to my needs.
However, I am having a hard time when including it into another flow; I have ensured to change the name of the control parameter; and I have played a bit within the macro by using the select tool to adapt the "test data" data formats to my end application.

I therefore need 2 strings, 1 fixed decimal and one date.

I have used the select option both in the macro and in the actual data source and then added the macro. For some reason it keeps changing the string to int32 anf the quote (fixed decimal) to byte.

Any reason for this?

Also, what do you mean by " i don't know if the desired output will be ok if you have more than 1 item per day.".
I will always have max 1 quote for each transport/client. Is that what you meant?

Thanks again,

Denise

patrick_digan
17 - Castor
17 - Castor

@Riccid Great questions! From my workflow, here is the macro configuration:

Capture.PNG

To answer your questions, let me explain exactly what is going on. First, I want to explain the choose field line. The first line says Choose Field: RowCount (String). This information is coming from the macro input tool in my macro. In the below picture, you can click on edit data to see the test data i had in there:

Capture.PNG

You would indeed find a field called RowCount. It's very important that you give it test data that correlates with the correct datatype. So if you want a string, use letters. If you want a number, use numbers with decimals. If you want a date, use dates. Since I used A, B, C, Alteryx has determined it is a string. You can verify that by clicking on the line coming out of the macro input, which would show you how it's defined all of my fields: 

Capture.PNG

The size isn't too important in that the macro will use the size of your incoming field. The other key piece of info is that on the macro input tool, I've checked the show field map box. This is key. When checked, the workflow has to identify which incoming field should be mapped the the fields setup in the macro input tool. 

 

Back to the workflow, the next thing on the macro configuration is the dropdown listing the fields. In the first picture, I have to map RowCount to one of my incoming fields. Since RowCount is a string, it will only give me options for string like fields (which is apparently nearly any data type since i see it will accept dates and numbers). By selecting Field1, something very important will happen when we run the workflow. Field1 goes into the macro, and it's automatically renamed RowCount. This is very useful in this case, because our macro is using the field RowCount. By using the show field map option, it allows me to pass Field1 from my data and the macro will process it correctly by first renaming it. Obviously you probably don't want the field coming out of your macro to always be named RowCount, so that's why i created a select tool in the macro and connected the macro input Q to the lightning bolt of the select tool. This added an action tool, which is configured to update select with reverse field map. This will rename the all our mapped fields (including rowcount in this case) back to their original names (which is Field1 in this case) before it sends it the macro output.

 

All that being said, I added another grouping field in the attached. The fact that your field is fixed decimal and the "Stock Price" field is double shouldn't be a problem since mapping fields will change the names but keep the original data type throughout. So it will treat it as fixed decimal. Hopefully this should be what you need.

 

As for the more than 1 per day, it sounds like you should be ok. If you were getting hourly quotes, I'm not sure that the macro would respond accordingly (there may just be some duplicating going on). It will handle multiple transport/clients per day.

Riccid
6 - Meteoroid

Hi Patrick!

So well explained, and getting the data right in the sample data within the macro seems to be the solution. However, I am a bit puzzled as I have introduced a select tool within the macro to change the sample data type before going through the process. With no success..

Also, I am not sure if there would be duplication even having a hourly quote. The macro works on a year basis(-1) until current row date; even an hourly quote system would only bring the max value for the past year worth of values, i suppose. Why do you suspect duplication? I might be missing something.

Thanks again,


D.

patrick_digan
17 - Castor
17 - Castor

Can you screenshot your macro? Feel free to PM if that makes more sense. You can hover over a name and click send message.

 

I guess hourly wouldn't be a problem. I just hadn't given it much thought and hadn't tested it. I assumed your data wasn't hourly, but I wanted to caveat it just in case!

Labels