Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Creating key that includes iterations of invoices

Hi all,

I have 2 very differente data sources that only have 1 field in common: the Invoice Number. Each invoice has several items, identified with a number called "position". However, in data source A, the first item is identified with the number 1, but in data source B it is identified with number 20 and subsequent items are may have different numbers. As a result, I cannot use a Invoice Number&Position to join both tables. I can't use the name of the item because they are called differently in each source.

The good thing is that both tables organize the Position of each invoice in the same order, like this:

LOAD * INLINE

[

Invoice,Position,Item

100,1,Apples

100,2,Bricks

100,3,Notebooks

100,4,laptops

100,5,Bag

100,6,Mouse,

100,6,Mouse,

,,,,

](delimiter is ',');


LOAD * INLINE

[

Invoice2,Position2,Item2

100,20,Red Apples

100,40,yellow bricks

100,70,110009 notebooks

100,90,Laptop dell 123

100,100,Leather bag 0939

100,150,Ergonomic mouse Sharkk22

100,150,Ergonomic mouse Sharkk22

,,,,

](delimiter is ',');


I would like to create an additional column whereas each iteration of the invoice gets counted starting for number 1.


How can I do that?


Thank you in advance for your help!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

I think you are missing a 1 in the else branch of your if() statement.

And use Rangesum() instead of Rangecount()

If(previous(invoice)=invoice, Rangesum(peek('ID'),1),1) as ID

I found also the issue with the Autonumber(), since Invoice is the same in both tables, we need to add a table identifier to create unique buckets per table and invoice values:


LOAD *,

Autonumber(Recno(), 'T1'&Invoice) as InvoiceDetailID,

//if(previous(Invoice)=Invoice, Rangesum(peek('ID'),1),1) as ID

INLINE

[

Invoice,Position,Item

200,20,Chairs

200,30,Chairs

100,1,Apples

100,2,Bricks

100,3,Notebooks

100,4,laptops

100,5,Bag

100,6,Mouse,

100,6,Mouse,

](delimiter is ',');





LOAD *,

Autonumber(Recno(), 'T2'&Invoice2) as InvoiceDetailID,

//If(previous(Invoice2)=Invoice2, Rangesum(peek('ID'),1),1) as ID

INLINE

[

Invoice2,Position2,Item2

100,20,Red Apples

100,40,yellow bricks

100,70,110009 notebooks

100,90,Laptop dell 123

100,100,Leather bag 0939

100,150,Ergonomic mouse Sharkk22

100,150,Ergonomic mouse Sharkk22

200,20,Chairs

200,30,Chairs

](delimiter is ',');


But with a lot of invoices, the Peek() method is perfoming better.

View solution in original post

6 Replies
Highlighted
MVP
MVP

Try something like

LOAD

     Autonumber(Recno(), Invoice) as InvoiceDetailID,

     ...

FROM Table;

Highlighted
Contributor II
Contributor II

Hi Stefan,

Thanks for your  help.  It does work! However, I have over 1 million rows and its taking over 1hr to process it. Is there any way to improve its speed?

Thanks,

Luis

Highlighted
Contributor II
Contributor II

It actually didn't work . If Invoice 200 happens to be before Invoice 100, it returns wrong values.

Highlighted
MVP
MVP

It shouldn't depend on the order of Invoices, only the order of Positions should matter.

Can you show a sample (INLINE table script or at least a screen shot) that demonstrates the issue?

The Autonumber could be slow if you have a lot of distinct Invoice numbers (buckets to create an autonumber set). The faster method would be to use good old sorted input table with Peek() function to create the position counter.

Highlighted
Contributor II
Contributor II

Hi Stefan,

When I run your script on a table that is not sorted, it returns wrong values.

LOAD * INLINE

[

Invoice,Position,Item

200,20,Chairs

200,30,Chairs

100,1,Apples

100,2,Bricks

100,3,Notebooks

100,4,laptops

100,5,Bag

100,6,Mouse,

100,6,Mouse,

,,,,

](delimiter is ',');


LOAD * INLINE

[

Invoice2,Position2,Item2

100,20,Red Apples

100,40,yellow bricks

100,70,110009 notebooks

100,90,Laptop dell 123

100,100,Leather bag 0939

100,150,Ergonomic mouse Sharkk22

100,150,Ergonomic mouse Sharkk22

200,20,Chairs

200,30,Chairs

,,,,

](delimiter is ',');

I tried using the following script:

If(previous(invoice)=invoice, rangecount(peek('ID'),1),) as ID

Seems like it works and its pretty fast. Any suggestion to make it better or more consistent?

Highlighted
MVP
MVP

I think you are missing a 1 in the else branch of your if() statement.

And use Rangesum() instead of Rangecount()

If(previous(invoice)=invoice, Rangesum(peek('ID'),1),1) as ID

I found also the issue with the Autonumber(), since Invoice is the same in both tables, we need to add a table identifier to create unique buckets per table and invoice values:


LOAD *,

Autonumber(Recno(), 'T1'&Invoice) as InvoiceDetailID,

//if(previous(Invoice)=Invoice, Rangesum(peek('ID'),1),1) as ID

INLINE

[

Invoice,Position,Item

200,20,Chairs

200,30,Chairs

100,1,Apples

100,2,Bricks

100,3,Notebooks

100,4,laptops

100,5,Bag

100,6,Mouse,

100,6,Mouse,

](delimiter is ',');





LOAD *,

Autonumber(Recno(), 'T2'&Invoice2) as InvoiceDetailID,

//If(previous(Invoice2)=Invoice2, Rangesum(peek('ID'),1),1) as ID

INLINE

[

Invoice2,Position2,Item2

100,20,Red Apples

100,40,yellow bricks

100,70,110009 notebooks

100,90,Laptop dell 123

100,100,Leather bag 0939

100,150,Ergonomic mouse Sharkk22

100,150,Ergonomic mouse Sharkk22

200,20,Chairs

200,30,Chairs

](delimiter is ',');


But with a lot of invoices, the Peek() method is perfoming better.

View solution in original post