Hi All,
I am new to Alteryx.
i need to transpose a data data based below requirement.
In below input table we have vendor name in 1st column and number of products vendor has delivered in 2nd column, respectively we have all the products delivery date and type of product delivered details in rest of the columns. i need an output as mention below in output table . Also, the challenging part here is even though Vendor A has 4 product to deliver he agreed to deliver only 3 products (you can see delivered date and product type filled only for 3 products) and we need show only three lines. if it has 4th product date and time then that should be printed again in new row. (0r else if i get fourth row with blank data that is also fine for me in this case.)
Dynamically if vendor has 5 products to deliver then product delivery date and type should be printed in 5 rows.
also, Vendor E has 0 products but i just need that line printed.
Input
Vendor | Products Delivered | Product 1 Delivery Date | Product Type 1 | Product 2 Delivery Date | Product Type 2 | Product 3 Delivery Date | Product Type 3 | Product 4 Delivery Date | Product Type 4 |
A | 4 | 7/11/2018 | Cloth | 7/11/2018 | Bag | 7/11/2018 | Bag | ||
B | 3 | 7/11/2018 | Bag | 7/11/2018 | Paper | 7/11/2018 | Paper | ||
C | 2 | 7/11/2018 | Paper | ||||||
D | 1 | 7/11/2018 | Box | ||||||
E | 0 |
Output
Vendor | Products Delivered | Date | Type |
A | 4 | 7/11/2018 | Cloth |
A | 4 | 7/11/2018 | Cloth |
A | 4 | 7/11/2018 | Cloth |
B | 3 | 7/11/2018 | Bag |
B | 3 | 7/11/2018 | Paper |
B | 3 | 7/11/2018 | Paper |
C | 2 | 7/11/2018 | Paper |
D | 1 | 7/11/2018 | Box |
E | 0 |
Many Thanks for your help.
Regards,
Shashank
Solved! Go to Solution.
See attached! You can do this fairly straightforward by utilising the Transpose & Cross-Tab tools... by Transposing all the column names in one field with their values in the next, and then a little data prep to determine the delivery number 1, 2, 3, 4 etc. and standardize the column names, you can Cross-Tab the data so that you have your delivery dates & product types next to each other with a record for each delivery.
Transpose and Cross-Tab are super-powerful tool if you get the hang of them - check out some of the help menu info and Tool Mastery articles for more information on configuration/etc.! Hope this helps!
Cheers,
NJ