Remove lines in load with identical keys

[CanOls]

Hi Qlick Community

I have a table as demonstrated below. 

I'd like to make a load of the table, but sort out values where lines have an identical Invoice_ID.

In case of an identical Invoice_ID, I'd like only load the newest line of that Id based on the date.  The invoice_ID can occur only once and up to  4 times. I'm aiming for a general solution that can handle all cases.

Any help is appreciated.

Casper 

[miskinmaz]

You can use to sort properties of the char at front end to sort the columns as you want

Also you can rearrange the column order in "Sorting" Properties to define the second sort order.

[CanOls]

That looks like QlikSense, and I'm using QlikView, I'm not sure that I have that option.

I'm might have worded my question wrong. I'm looking for a way to remove the lines I don't need in the load.

[marcus_malinow]

You can use an Exists() clause in your data load.

For example, your Invoices table load might be something like this:

Invoices:

LOAD

INVOICE_ID, 

USER_NAME, 

TIME_STAMP, 

, [all your other fields here]

FROM

[Your Data Source]

WHERE NOT Exists(INVOICE_ID);

This will load only the first instance of each INVOICE_ID

[CanOls]

Thanks for the reply.

But what if I don't want to load the first instance of the ID, but the newest, according to the time stamp?

[Zhandos_Shotan]

Hi!

1. Load:

[Table with max timestamp invoice  only]

noConcatenate Load 

Invoice_ID,

Max(TIME_STAMP) as TIME_STAMP

resident Tab

group by Invoice_ID;

2. Join table and mark records with max timestamp by invoice:

Join(Tab)

Invoice_ID,

TIME_STAMP,

1 as LoadMaxTSFlag

resident [Table with max timestamp invoice  only];

3. Load only marked records:

noConcatenate load *

resident Tab

where LoadMaxTSFlag=1;

Regards

[siddheshmane]

Hi,

You might want to check FirstSortedValue() in the Load Script

Here's and example:

https://community.qlik.com/t5/QlikView-Layout-Visualizations/FirstSortedValue/td-p/317853 

[CanOls · Accepted solution]

Thanks for your reply.

I didn't get to test it out, because a co worker for a solution.

I ended up using group by on INVOICE_ID and then using LastValue in another table that I joined with it.