8 Replies Latest reply: Nov 15, 2015 4:46 PM by Stefan Wühl RSS

    Generating the missing data with previous data

      Hi All,

       

      I have a scenario , if my previous data is missing it should copy the last value available in the previous row based on a condition.

       

      Lets say I have a table shown below and if my customer ,branch, and month should be same as previous rows only then it should copy the revious value. In this case it should copy 3000 for all the below rows. I am trying to do this in the script.

       

       

      Current

       

      CustomerBranchMonthValue
      Customer1Branch1Jan1000
      Customer1Branch1Jan
      Customer1Branch1Jan
      Customer1Branch1Jan
      Customer2Branch1Feb2000
      Customer2Branch2Feb2100
      Customer3Branch2Feb3000
      Customer3Branch2Feb
      Customer3Branch2Feb
      Customer3Branch2Feb
      Customer3Branch2Feb
      Customer3Branch2Feb

       

       

      After I made a condition like if(customer = peek(customer) and Branch = peek(Branch) and Month = Peek(Month),peek(value))

       

      It just copies the value to the immediate below row but I want it to be copied to all the rows as per my request. here 3000 should be copied to the below 5 lines as it satisfies my condition. Same with 1000 it should be copied to immediate 3 rows.

       

      Can any one help me with this please.

       

      Thanks,

      Lalitha

        • Re: Generating the missing data with previous data
          Stefan Wühl

          Try this assuming your table is sorted as in above sample, if not, use a ORDER BY clause in your original table LOAD.

           

          LOAD Customer,

               Branch,

               Month,

          //    Value as Value2,

               If(Len(Trim(Value)),

                        Value,

                        if(Customer = peek(Customer) and Branch = peek(Branch) and Month = Peek(Month), peek(Value))

               )                AS Value

          FROM

          [https://community.qlik.com/thread/191491]

          (html, codepage is 1252, embedded labels, table is @1);

            • Re: Generating the missing data with previous data

              Hi Swuehl,

               

              Thanks for your reply. I am doing the same thing in my script and using the order by as well.

               

              Please find the attached qlikview file.

               

              Due to same rows for customer3 it is not showing the multiple rows in Table box.

               

              But I hope you have understood my requirement.I want to copy the data if my above mentioned condition satisfies irrespective of any number of rows.

               

              Thanks alot for your time..

                • Re: Generating the missing data with previous data
                  Stefan Wühl

                  It should work if you use the script I posted above and not only part of.

                   

                  In addition (but that's not failing your script), I've converted the Month to a dual value, so it sorts chronological.

                    • Re: Generating the missing data with previous data

                      Hi Swuehl,

                       

                      Thanks for your reply..  I have changed the data little bit so it would be more clear.

                       

                      the value 14850 of week 42 should be copied to week 43 and week 44.

                       

                      Could you please help me with this..

                       

                      Thanks for your time.

                        • Re: Generating the missing data with previous data
                          Stefan Wühl

                          Yes. Understood. I believe I am not posting my solution a third time, unchanged to the first (ok, except the change for Peek(Week) = Week removed in the condition, because it's a new requirement that you want to get the value from previous week).

                           

                          Can you tell me what's wrong with my solution, and why you keep changing the script in a way that's it not working anymore?

                           

                           

                          I do get this result:

                          Customer Branch Week Value TMP
                          Customerbranch12014/301038810388
                          Customerbranch12014/3110388
                          Customerbranch12014/321038810388
                          Customerbranch12014/331047210472
                          Customerbranch12014/3410472
                          Customerbranch12014/351161611616
                          Customerbranch12014/361293112931
                          Customerbranch12014/3712931
                          Customerbranch12014/381344813448
                          Customerbranch12014/391382813828
                          Customerbranch12014/401417214172
                          Customerbranch12014/4114172
                          Customerbranch12014/421485014850
                          Customerbranch12014/4314850
                          Customerbranch12014/4414850
                          Customerbranch12014/451586415864
                          Customerbranch12014/461637116371
                          Customerbranch12014/4716371
                          Customerbranch12014/481716917169
                          Customerbranch12014/491784317843
                          Customerbranch12014/5017843
                          Customerbranch12014/511835018350
                          Customerbranch12014/521869418694
                          Customerbranch12015/011892218922

                           

                          TMP is only to show the original values of 'Value' as the are read in. You can remove the TMP field from the script if you want:

                           

                          LOAD

                          Customer,Branch,Week, // Value as TMP,

                           

                          If(Len(Trim(Value)), Value,

                            if(Customer= peek(Customer) and Branch = peek(Branch) ,peek(Value))) as Value

                           

                          Resident Test order by Customer,Branch,Week;

                  • Re: Generating the missing data with previous data
                    Andrea Longhi

                    Hello Latitha,

                    you can fill missing values using Qlikview transformation functions.

                    Select "Enable transformation Step" in file wizard

                    EnableTransf.png

                    then select "Fill"  Tab

                    Fill.png

                    ...and click on "Fill..." Button

                    Fillbutton.png

                    Now you can specify your fill strategy selecting target column (4, in your case), cell condition (is empty) and fill type (Above, in your case)

                    fillcells.png

                     

                    then press "Next" button and finally press "Finish" button.

                     

                    At the end the load instruction have to looks like the following script:

                     

                     

                    LOAD Customer,
                    Branch,
                    Month,
                    Value
                    FROM
                    FillData.xlsx
                    (
                    ooxml, embedded labels, table is Sheet1, filters(
                    Replace(4, top, StrCnd(null))
                    ));

                    Reload data and...good luck !!!


                    Best regards

                    Andrea