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 itemCodeState18-03-20200000040321/0019118State 818-03-20200000040321/0019128State 818-03-20200000040321/0019138State 818-03-20200000040307/0006018State 828-03-20200000040321/0019138State 815-03-2020SS00445444520022State 218-03-2020SS00445444520022State 218-03-2020SS00445444520018State 821-03-2020SS00445444520028State 818-03-20200000040012/0003014State 421-03-20200000040012/0003014State 4 State: StateState 1State 2State 3State 4State 5State 6State 7State 8State 9State 10State 11State 12State 13State 14State 15State 16State 17State 18State 19State 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 200000040321/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 Data.xlsx

transporting data and min max dates based on values

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

Date	Order No/Line item	Code	State
18-03-2020	0000040321/001911	8	State 8
18-03-2020	0000040321/001912	8	State 8
18-03-2020	0000040321/001913	8	State 8
18-03-2020	0000040307/000601	8	State 8
28-03-2020	0000040321/001913	8	State 8
15-03-2020	SS0044544452002	2	State 2
18-03-2020	SS0044544452002	2	State 2
18-03-2020	SS0044544452001	8	State 8
21-03-2020	SS0044544452002	8	State 8
18-03-2020	0000040012/000301	4	State 4
21-03-2020	0000040012/000301	4	State 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 item

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

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

Data.xlsx

Developer

Date Time

Transformation

Accepted answers

markcurry

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

All comments

markcurry

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

sagarkharpude

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".

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703