24 Replies Latest reply: Jul 6, 2015 5:21 AM by jagan mohan rao appala RSS

    Filling Empty Cells

      Hello experts,

       

      I am trying to do the following.  I have a table that has a column of vendor IDs and another column with line numbers.  The problem is that is there are multiple lines, which is almost always the case, the vendor number will only appear in the last line.  I also have a copy of this vendor number column as a new column.  I would like to pupulate the cells in the new row with the vendor number of the last line in the invoice.  For example:

       

      I have:

       

      Invoice     Line Number     Vendor1     NewVendor    

      1               1             

      1               2

      1               3                         101

      2               1         

      2               2

      2               3

      2               4    

      2               5                         102

       

      What I would lke is:        

       

      Invoice     Line Number     Vendor1     NewVendor    

      1               1                                              101

      1               2                                              101   

      1               3                         101               101

      2               1                                              102

      2               2                                              102

      2               3                                              102

      2               4                                              102

      2               5                         102               102

       

      Any ideas how I could perhaps sort descending and peek back??

       

      Thanks,

      Dave

        • Re: Filling Empty Cells
          ioannis giakoumakis

          create a separate table with only invoice and max(vendor1) and then left join it to the original table.

          you could do the same but instead of left joining you could use ApplyMap

          • Re: Filling Empty Cells
            Sunny Talwar

            Try this script:

             

            Table:

            LOAD Invoice,

              [Line Number],

              If(Len(Trim(Vendor1)) = 0, 0, Vendor1) as Vendor1;

            LOAD * Inline [

            Invoice,    Line Number,    Vendor1

            1,              1,           

            1,              2,

            1,              3,                        101

            2,              1,       

            2,              2,

            2,              3,

            2,              4,   

            2,              5,                        102

            ];

             

            NewTable:

            LOAD *,

              If(Invoice = Peek('Invoice'), Alt(Peek('NewVendor'), Vendor1), Vendor1) as NewVendor

            Resident Table

            Order By Invoice, Vendor1 desc;

             

            DROP Table Table;

             

            Output:

             

            Capture.PNG

            • Re: Filling Empty Cells
              Alessandro Saccone

              Try this script

               

              //Your Data (Do not load New Vendor)
              Tab:
              LOAD * Inline [
              Invoice, Line Number, Vendor1
              1, 1, 
              1, 2,
              1, 3, 101
              2, 1, 
              2, 2,
              2, 3,
              2, 4, 
              2, 5, 102
              ]
              ;

              //Retrieve the last invoice line
              Left Join
              LOAD Invoice, Max([Line Number]) as maxi Resident Tab Group By Invoice;

              Left Join
              LOAD Invoice, Max(Vendor1) as NV Resident Tab Where [Line Number] = maxi Group By Invoice;

                • Re: Filling Empty Cells

                  Alessandro,

                   

                  I tried your version, which seems to be what Ioannis was saying, replacing "Tab" with my datasource but get a message upon reload that the variable "maxi" could not be found....wouldn't the LOAD .. AS maxi... take care of that (the error is in the 2nd join=.  Thanks for any ideas.

                   

                  Dave

                • Re: Filling Empty Cells
                  jagan mohan rao appala

                  Hi,

                   

                  Try like this

                   

                  Data:
                  LOAD * Inline [
                  Invoice, Line Number, Vendor1
                  1, 1, 
                  1, 2,
                  1, 3, 101
                  2, 1, 
                  2, 2,
                  2, 3,
                  2, 4, 
                  2, 5, 102
                  ]
                  ;


                  LEFT JOIN (Data)

                  LOAD

                  DISTINCT Invoice, Vendor1 AS NewVendor

                  RESIDENT Data;


                  Hope this helps you.


                  Regards,

                  Jagan.


                    • Re: Filling Empty Cells

                      I see were you are going with this but I kind of simplified the Invoice Number part.  It is in fact, not unique by itself and Vendor is not distinct at all.  Invoice Number & BusinessUnit & Year is distinct and is automagically joined by QlikView....I am new to this, coming from an SQL background where I would join everything myself.  So my first thought is this will not work but I could give it a try if I can't get the other problem worked out in an easy way.  Thanks!  Dave

                        • Re: Filling Empty Cells
                          jagan mohan rao appala

                          Hi,

                           

                          In Qlikview the join is automatic based on the common field names, you cannot control that. You have to rename the fields accordingly as per the join requirement.  If you share the exact sample data and your expected output then it would be easier to provide the solution.

                           

                          Regards,

                          jagan.

                            • Re: Filling Empty Cells

                              OK...what I have done so far is to create a join between the invoice header and the invoice lines (as they are stored in two different tables) and added a MAX(LineNumber) AS TopLine to that data set.....so now I have a new table that is only the 3 criteria to make it JOIN (combined are unique: InvoiceNumber, BusinessArea and Year) and added a "MAX(LineNumber) AS TopLine" in usinf SQL SELECT and called it MainData in QlikView.

                               

                              Now, in QlikView preferably, but if I have to do it in SQL that is OK too.  I want to take the LineItem table, JOIN with the MainData table I created above, basicially joining WHERE InvoiceNumber (LineItem) = InvoiceNumber (MainData) AND BusinessArea (LineItem) = BusinessArea (MainData) AND YEAR (LineItem) = YEAR (MainData) and filling each of these lines with MAX(LineNumber) aka TopLine from above.

                               

                              Does that make it clearer or worse.  I was asked to do as much in QlikView as possible but since I draw from an SQL database for most data, I need quite a bit of SQL.

                               

                              Thanks again,

                              Dave

                          • Re: Filling Empty Cells
                            arjun rao

                            Hi Jagan,

                             

                            I tried with your script. It is working fine. When I use table box, Null is getting for NewVendor.

                             

                            Kindly advise.

                             

                            Capture.JPG

                          • Re: Filling Empty Cells

                            Ioannis,

                             

                            • Re: Filling Empty Cells
                              arjun rao

                              Hi David Gilligan,

                              Can you select Correct Answer to close the thread.Thank you.

                                • Re: Filling Empty Cells

                                  Yes, I hope to soon.  I may have over simplified a bit bu just stating "Invoice" when 3 fields (one is the invoice) need to match before it is a unique key, so anything relying JUST on invoice (and not Business Area and Year) will not work as the invoice number gets reused yearly and by business area (I don't know why this is, but it is and I must accept this).  Hopefully, I can modify one of the above and post what I got and mark an answer.  Thanks, Dave

                                    • Re: Filling Empty Cells
                                      arjun rao

                                      Hi David Gilligan,

                                      What is your issue? Can you post with sample data?

                                      If your issue resolved, please close the thread. Thanks.

                                        • Re: Filling Empty Cells

                                          Here is revised sample data:

                                           

                                          Invoice     Year     BusinessArea     Line Number     Vendor1     NewVendor    

                                          1          2015           1                                  1             

                                          1          2015           1                                  2

                                          1          2015           1                                  3                  101

                                          2          2015           1                                  1         

                                          2          2015           1                                   2

                                          2          2015           1                                   3

                                          2          2015           1                                   4    

                                          2          2015           1                                   5                 102

                                          3          2015           1                                   1                   

                                          3          2015           1                                   2                 101

                                          1          2014           1                                   1                        

                                          1          2014           1                                   2                 102

                                          1          2015           2                                   1          

                                          1          2015           2                                   2

                                          1          2015           2                                   3                 104

                                              

                                           

                                          What I would lke is:        

                                           

                                             

                                          Invoice     Year     BusinessArea     Line Number     Vendor1     NewVendor    

                                          1          2015           1                                  1                                  101

                                          1          2015           1                                  2                                  101

                                          1          2015           1                                  3                  101          101

                                          2          2015           1                                  1                                  102

                                          2          2015           1                                   2                                 102

                                          2          2015           1                                   3                                 102

                                          2          2015           1                                   4                                 102

                                          2          2015           1                                   5                 102          102

                                          3          2015           1                                   1                                 101

                                          3          2015           1                                   2                 101          101

                                          1          2014           1                                   1                                 102

                                          1          2014           1                                   2                 102          102

                                          1          2015           2                                   1                                 104

                                          1          2015           2                                   2                                 104

                                          1          2015           2                                   3                 104          104

                                           

                                          WHERE the combination of Invoice, Year & Business Area is unique, not just invoice number.  My bad in over-simplifying the specs.  So I may have say 12 invoice #1s, 1 for each of 4 different business areas and 4 each business area over the past 4 years.

                                           

                                          Thanks,

                                          Dave

                                            • Re: Filling Empty Cells
                                              jagan mohan rao appala

                                              Hi,

                                               

                                              Try this script

                                               

                                              Data:

                                              LOAD

                                              *,

                                              Invoice & '-' & BusinessArea  & '-'  & Year AS Key

                                              INLINE [

                                              Invoice,     Year,     BusinessArea,     Line Number,     Vendor1

                                              1,          2015,           1,                                 1, 

                                              1,          2015,           1,                                  2,

                                              1,          2015,           1,                                  3,                  101

                                              2,          2015,           1,                                  1,        

                                              2,          2015,           1,                                   2,

                                              2,          2015,           1,                                   3,

                                              2,          2015,           1,                                   4,   

                                              2,          2015,           1,                                   5,                 102

                                              3,          2015,           1,                                   1,                  

                                              3,          2015,           1,                                   2,                 101

                                              1,          2014,           1,                                   1,                       

                                              1,          2014,           1,                                   2,                 102

                                              1,          2015,           2,                                   1,         

                                              1,          2015,           2,                                   2,

                                              1,          2015,           2,                                   3,                 104];

                                               

                                               

                                              LEFT JOIN

                                              LOAD

                                              Key,

                                              Vendor1 AS NewVendor

                                              RESIDENT Data

                                              WHERE Len(Trim(Vendor1)) >0;

                                              • Re: Filling Empty Cells
                                                arjun rao

                                                Hi David Gilligan,

                                                select Correct Answer to jagan mohan reply. Thanks.

                                                You wrongly selected your reply.