Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to transform my data from table 1 to table 2. What's the best way to script it?
table 1
Customer ID | Store 1 | Store 2 | Store 3 |
Customer 1 | apple,pear | apple,pear | pear |
Customer 2 | apple,orange | pear | orange |
table 2
Customer ID | Item | Store 1 | Store 2 | Store 3 |
Customer 1 | apple | Y | Y | N |
Customer 1 | pear | Y | Y | Y |
Customer 2 | apple | Y | N | N |
Customer 2 | pear | N | Y | N |
Customer 2 | orange | Y | N | Y |
I would start with transforming the table1 from a cross table into a straight table, then creating a record per each comma separated item value:
DECROSS:
CROSSTABLE (Store, Item)
LOAD [Customer ID], [Store 1], [Store 2], [Store 3]
RESIDENT Table1;
STRAIGHT:
LOAD [Customer ID],
Store,
Subfield(Item, ',') as Item
RESIDENT DECROSS;
Now you should have a table with fields Customer ID, Store and Item, one item per record.
IMHO, this is how you should store your data in QV data model, see also
If you want to show the data as you show for table 2, create a pivot table chart with dimensions Customer ID, Store and Item and create one expression:
=If(Count(Item) >0,'Y','N')
See if this script can help -
//Sample Data - add Comma after each item to be picked up by subfield individually
table1:
Load [Customer ID],Store1 & ',' as Store1, Store2 & ',' as Store2, Store3 as Store3;
Load * inline [
Customer ID, Store1, Store2, Store3
Customer1, "apple,pear","apple,pear",pear
Customer2,"apple,orange",pear,orange
];
//Created Item field from Store fields
Left Join(table1)
Load
[Customer ID],
subfield(Store1 & Store2 & Store3,',') as Item
resident table1;
//Created new Store data by matching item in old store fields
NoConcatenate
F1:
Load
[Customer ID],
Item,
if(SubStringCount(Store1,Item)>0,'Y','N') as Store1,
if(SubStringCount(Store2,Item)>0,'Y','N') as Store2,
if(SubStringCount(Store3,Item)>0,'Y','N') as Store3
resident table1;
//Removed old store data
NoConcatenate
F2:
Load
[Customer ID],
Item,
Store1,
Store2,
Store3
resident F1
Where len(Store1)=1 and len(Store2)=1 and len(Store3)=1;
Drop Table table1,F1;