Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nsnybs21qv
New Contributor II

Merge List with Flags

Hi

I need to transform my data from table 1 to table 2. What's the best way to script it?

table 1

 

   

Customer IDStore 1Store 2Store 3
Customer 1apple,pearapple,pear pear
Customer 2apple,orangepearorange

 

table 2

   

Customer IDItemStore 1Store 2Store 3
Customer 1appleYYN
Customer 1pearYYY
Customer 2appleYNN
Customer 2pearNYN
Customer 2orangeYNY
2 Replies
MVP
MVP

Re: Merge List with Flags

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
Honored Contributor III

Re: Merge List with Flags

See if this script can help -

Capture.JPG

//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;

Community Browser