Filling Empty Cells

[#1]

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

[qlikviewwizard]

Hi sunindia,

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

What Alt function will do in the above function?

Thanks in advance.

[qlikviewwizard]

Hi Jagan,

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

Kindly advise.

[#4]

Ioannis,

In keeping with your join idea, I have created a table with the three variables I need to make the Invoice unique (in order for it to be unique, I need to have the invoice number, business unit and year match each other) and another field with just the number of the highest line number (the one that contains the vendor).  Given that one table has the unique combination to create a unique key and the highest line number and the second table also has the unique combination of keys, the line number and vendor ID, how would I construct such a join in Qlickview, where it would pull the vendor number down to all rows?  Thanks in advance, Dave

[qlikviewwizard]

Hi davidjgilligan,

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

[#6]

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

[qlikviewwizard]

Hi davidjgilligan,

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

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

[#8]

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

[jagan · Accepted solution]

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;

[qlikviewwizard]

Hi davidjgilligan,

Please follow jagan

's Advise and close the thread. Thanks.

[#11]

OK Wizard, I am going as fast as I can....the field names are not vendor and the script will not work as indicated because it is not an inline load.  I need more than 1 minute to test this without 2 emails every post 30 seconds after posted.

That being said, 1000 thanks to Jagan....after some modifications it appears to work perfectly!  Thanks again.  Dave