community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

transporting data and min max dates based on values

sagarkharpude
7 - Meteor
‎04-01-2020 02:21 AM

Hi Team,

 

I am new in Alteryx. Please find the attached excel. I have two sheets: one is data and other one is State.  and want to do transform the data like output. In raw data, I don't have all the states from 1 to 20 but I want to display all the States as a column and then want to put respective date on State column against Order No/Line item.

 

Logic : For one Order No/Line item we should have one row only. If same Order No/Line item is repeating then and State is 8 then we should put maximum Date in Column "State 8". If State is 2 then we should put minimum date in column "State 2". If State is 4 then we should put Maximum date in column "State 4". 

 

Thanks for looking into this. I appreciate your help.

 

Thanks,

Sagar Kharpude

 

Data

DateOrder No/Line itemCodeState
18-03-20200000040321/0019118State 8
18-03-20200000040321/0019128State 8
18-03-20200000040321/0019138State 8
18-03-20200000040307/0006018State 8
28-03-20200000040321/0019138State 8
15-03-2020SS00445444520022State 2
18-03-2020SS00445444520022State 2
18-03-2020SS00445444520018State 8
21-03-2020SS00445444520028State 8
18-03-20200000040012/0003014State 4
21-03-20200000040012/0003014State 4

 

State:

State
State 1
State 2
State 3
State 4
State 5
State 6
State 7
State 8
State 9
State 10
State 11
State 12
State 13
State 14
State 15
State 16
State 17
State 18
State 19
State 20

 

Expected Output:

Order No/Line itemState 1State 2State 3State 4State 5State 6State 7State 8State 9State 10State 11State 12State 13State 14State 15State 16State 17State 18State 19State 20
0000040321/001911       18-03-2020            
0000040321/001912       18-03-2020            
0000040321/001913       28-03-2020            
0000040307/000601       18-03-2020            
SS0044544452002 15-03-2020     21-03-2020            
SS0044544452001       18-03-2020            
0000040012/000301   21-03-2020                
Preview file
19 KB
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

4 REPLIES 4
markcurry
12 - Quasar
‎04-01-2020 03:51 AM

I'm not sure I understand the logic you're trying to apply, but have a look at this workflow it should point you in the right direction....

Create Output Table.yxmd
Reply
sagarkharpude
7 - Meteor
‎04-01-2020 04:37 AM

Hi,

 

Thanks a lot for your efforts. Could you please tell me how to apply the Min & Max Date logic based on the State?

For e.g. For 0000040321/001913 it should give me Max Date for State 8 i.e. 28-03-2020. Currently it's giving me the first Date(18-03-2020). If State is 2 then it should give minimum date in column "State 2". If State is 4 then put Maximum date in column "State 4". 

 

sagarkharpude_0-1585740831637.png

 

Reply
0 Likes
markcurry
12 - Quasar
‎04-01-2020 04:53 AM

So for States 2, and 8 you want the Minimum date, but for State 4 you want the Maximum date, is that right?   If that's the case, they split out your Data to the states you want to have Min\Max dates, then join then back together, and create the table.  I've updated the workflow...

Create Output Table.yxmd
Reply
sagarkharpude
7 - Meteor
‎04-01-2020 05:17 AM

Thanks a lot. 🙂

Reply
learn more about the python upgrade coming in may 2024
Labels