5 Replies Latest reply: Jun 11, 2018 3:23 PM by sahiba kaur kohli RSS

    Re-create table in Qlik Sense

    sahiba kaur kohli

      Hi All,

      I have an input table as below that shows if a customer has bought Bag/Pen or Books.

       

      Cust    Bag       Pen       Books

      A         Yes       No          No

      B         No        No          No

      C         No       Yes         No

      D         Yes      Yes         No

       

      I want my output as below:

      Customer  Status

      A               Bag

      C               Pen

      D               Bag

      D               Pen

        • Re: Re-create table in Qlik Sense
          Petter Skjolden

          In the load script you can transform the input table to the output like this:

           

           

          DATA:

          LOAD

            Cust,

            Item

          WHERE

            Bought = 'Yes';

          LOAD

            Cust,

            Pick(IterNo(),'Bag','Pen','Books') AS Item,

            Pick(IterNo(),Bag,Pen,Books) AS Bought

          INLINE [

          Cust    Bag      Pen      Books

          A        Yes      No          No

          B        No        No          No

          C        No      Yes        No

          D        Yes      Yes        No

          ] (delimiter is Spaces)

          WHILE

            IterNo()<=3

          ;



          If you read from a file it would look like this:

           

          DATA:

          LOAD

            Cust,

            Item

          WHERE

            Bought = 'Yes';

          LOAD

            Cust,

            Pick(IterNo(),'Bag','Pen','Books') AS Item,

            Pick(IterNo(),Bag,Pen,Books) AS Bought

          FROM

             "a-file.txt" (txt)

          WHILE

            IterNo()<=3

          ;

          • Re: Re-create table in Qlik Sense
            sahiba kaur kohli

            Hi Petter, Thanks for providing solution. I tried the script (by uploading the file) but there is an error. I am attaching the error.

            I apologise if I am asking very basic questions, I am pretty new to Qlik Sense.

            Error - Qlik Sense.png

            • Re: Re-create table in Qlik Sense
              sahiba kaur kohli

              Hey Petter,

               

              Your code is working absolutely fine, Its exactly what i wanted. May be i am making some mistake while uploading a file.

               

              DATA:

              LOAD  Cust,  Item WHERE  Bought = 'Yes';

               

               

              LOAD

              Cust,   Pick(IterNo(),'Bag','Pen','Books') AS Item,  Pick(IterNo(),Bag,Pen,Books) AS Bought

              from "C:\Users\admin\Documents\Book12TRIAL.txt" (txt)

              WHILE IterNo()<=3;

               

              Is this is the correct way of writing the path of file ??