Merge List with Flags

[Anonymous]

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

[swuehl]

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

The Crosstable Load

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')

[Digvijay_Singh]

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;