topic Re: Remove lines in load with identical keys in QlikView

Remove lines in load with identical keys

CanOls — Sat, 16 Nov 2024 02:51:46 GMT

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

Re: Sort load with identical keys

miskinmaz — Wed, 31 Jul 2019 08:54:17 GMT

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.

Re: Sort load with identical keys

CanOls — Wed, 31 Jul 2019 08:59:19 GMT

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.

Re: Remove lines in load with identical keys

marcus_malinow — Wed, 31 Jul 2019 09:11:21 GMT

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

Re: Remove lines in load with identical keys

CanOls — Wed, 31 Jul 2019 10:43:28 GMT

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?

Re: Remove lines in load with identical keys

Zhandos_Shotan — Wed, 31 Jul 2019 11:11:15 GMT

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

Re: Remove lines in load with identical keys

siddheshmane — Wed, 31 Jul 2019 11:21:51 GMT

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

Re: Remove lines in load with identical keys

CanOls — Thu, 01 Aug 2019 07:30:54 GMT

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.