Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
CanOls
Contributor III
Contributor III

Remove lines in load with identical keys

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 

2.png

Labels (4)
1 Solution

Accepted Solutions
CanOls
Contributor III
Contributor III
Author

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. 

 

View solution in original post

7 Replies
miskinmaz
Creator III
Creator III

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

filter.JPG

 

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

CanOls
Contributor III
Contributor III
Author

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
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

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
Partner
Partner

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
Creator
Creator

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
Contributor III
Contributor III
Author

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. 

 

View solution in original post