7 Replies Latest reply: Jan 18, 2018 9:48 AM by Simone Trabattoni RSS

    Help with Qlik Sense load Script

    Paul Barrett

      Hello,

       

      I have 5 files,

      1. List of Item Numbers

      2. List of Item Numbers and address

      3. List of Item Numbers and address

      4 List of Item Numbers and address

      5. List of Item Numbers and address

       

      What I need to do is create a table in the load script that contains all the item numbers from file 1, then joins the addresses from the other 4 files only when the item number appears in the first file.

       

      Can anyone help?

       

      Thanks Paul

        • Re: Help with Qlik Sense load Script
          surendra j

          Load first table

          concatinate

          load all 4 tables(it will auto concatinate if number of fields and names are same)

          where exits( item numbers);

          • Re: Help with Qlik Sense load Script
            surendra j

            To simplify!!

            load * from Table 1;

            concatinate

            Load * from Table2;

            Load * from Table3;

            Load * from Table4;

            Load * from Table5;

            where exits( item numbers);

            Here all 4 tables are auto-concatinated and Resulted into one single table.




            • Re: Help with Qlik Sense load Script
              Andrea Gigliotti

              you can do as below:

               

              load

              itemNo from Table1;

              left join

              load

              itemNo,

              address1

              from Table2;

              left join

              load

              itemNo,

              address2

              from Table3;

              left join

              load

              itemNo,

              address3

              from Table4;

              left join

              load

              itemNo,

              address4

              from Table5;

               

              Hope it helps.

              • Re: Help with Qlik Sense load Script
                Simone Trabattoni

                Hi, I've tried to create some fake data, to see if the result is going to be as you'd like to have. Probably my solution is the longest, and there are also some cases to see.

                Hope it helps!

                 

                // load the first table

                numbers:

                // the four has not address, it is going to appear without address in the

                // final result

                load*Inline

                [numbers

                1

                2

                3

                4

                5

                ];

                 

                // store it in one folder and free some space

                Store numbers Into [lib://store/numbers.qvd](qvd);

                drop table numbers;

                 

                 

                // load all the addresses in one table

                address:

                // this one does not exist in the numbers, so in the final result is not going to appear.

                load*Inline

                [numbers,address

                6,f

                ]

                ;


                concatenate

                load*Inline

                [numbers,address

                1,a

                ]

                ;


                concatenate

                load*Inline

                [numbers,address

                5,e

                ]

                ;

                 

                concatenate

                load*Inline

                // this is a duplicate, it is going to appear "twice" in the final result

                [numbers,address

                1,a

                ]

                ;

                 

                concatenate

                load*Inline

                [numbers,address

                2,b

                3,c

                ]

                ;

                 

                // store the addresses and free some memory

                Store address Into [lib://store/address.qvd](qvd);

                drop table address;

                 

                // load the stored files, and right join them, having only the addresses whom

                // number exists in the number table.

                LOAD

                    numbers

                FROM [lib://store/numbers.qvd]

                (qvd);

                 

                JOIN

                 

                LOAD

                    numbers,

                    address

                FROM [lib://store/address.qvd]

                (qvd) WHERE EXISTS(numbers);