2 Replies Latest reply: Mar 8, 2016 1:31 AM by Digvijay Singh RSS

    Merge List with Flags

    Yvonne Han

      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
        • Re: Merge List with Flags
          Stefan Wühl

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

          • Re: Merge List with Flags
            Digvijay Singh

            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;