5 Replies Latest reply: May 14, 2012 1:38 AM by Nilesh Gangurde RSS

    fill the missed fields during scriptload

      Hi together,

       

       

      i have a urgent problem and did not how i can handle it. Daily, I get a origin excel source document to load in qlikview. unfortunately, did

      this excel document not contain all datarows that i need. In this case the following datarows are not listed ( look at my green highlited datarows ) 

       

      How can i fill the missed datarows during the scriptload ?

       

      Excelsource:

      source.PNG

       

      and this is the target table that i want to build in qlikview:

      target.PNG

       

      I am very grateful for a practical advice. An example would be nice. Thank you !

       

      Best Regards

        • fill the missed fields during scriptload
          Nilesh Gangurde

          Hi Honour,

           

          Make the inline table of the three rows and then concate that table with the your excelsource table.

           

          The script will be as follows:

           

          Load * from [Excelsource];

           

          concatenate

           

          Load * inline [

          Product,country,quartal,year,value

          alpha,france,1,2011,0

          bravo,germany,2,2011,0

          bravo,germany,4,2011,0

          ]

           

           

          Hope this helps.

           

          Regards,

          Nilesh Gangurde

          • fill the missed fields during scriptload
            Stefan Wühl

            If you don't know upfront which lines are missing, you could create a table with all possible combinations for Product/Country, Year and Quartal. Then join this table to your read in data and fill in the missing zeros.

             

            Maybe like this (I create some dummy data first, since I can't easily copy your sample data from the bitmap image):

             

            //Create some sample data

            INPUT:

            LOAD

            chr(65+floor(RAND()*4)) as Product,

            chr(65+floor(RAND()*4)) as Country,

            ceil(RAND()*4) as Quarter,

            2011+floor(RAND()*2) as Year,

            ceil(RAND()*100) as Value

            autogenerate 25;

             

            //Create a table with all possible combinations

            PRODUCTCOUNTRY:

            LOAD distinct Product, Country resident INPUT;

             

            join (PRODUCTCOUNTRY) load Distinct Year resident INPUT;

            join (PRODUCTCOUNTRY) load Distinct Quarter Resident INPUT;

             

            //join the created table with the original data

            left join (PRODUCTCOUNTRY) load * Resident INPUT;

             

            drop table INPUT;

             

            // fill in missing zeros

            RESULT:

            noconcatenate LOAD Product, Country, Year, Quarter, if(len(Value),Value,0) as Value Resident PRODUCTCOUNTRY order by Product, Country, Year, Quarter;

             

            drop table PRODUCTCOUNTRY;