Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thepiapus
Contributor II
Contributor II

Filter data in the load script

Hi,

 

I have a very simple script

Load	[Item No_],
		[Expiration Date],
		[Lot No_];
SQL Select
    ILE.[Item No_],
    Min(ILE.[Expiration Date]) As [Expiration Date],
    ILE.[Lot No_]
From
    FARMADENT_NAV_90_PROD.dbo.[Farmadent PROD$Item Ledger Entry] ILE
Group By
    ILE.[Item No_],
    ILE.[Lot No_]
Having
    Sum(ILE.Quantity) > 0;

I want to keep only one record for each [Item No_] with Min([Expiration Date]).  Was trying with FirstSortedValue without success.

Thx in advance, Igor

1 Solution

Accepted Solutions
sunny_talwar

Try a Right Join after your table load

Tmp:
LOAD [Item No_], 
     [Expiration Date], 
     [Lot No_]
FROM
[C:\Users\ipohleven\Desktop\sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Right Join (Tmp)
LOAD [Item No_],
	 Min([Expiration Date]) as [Expiration Date]
Resident Tmp
Group By [Item No_];

 

View solution in original post

8 Replies
sunny_talwar

You are looking to do this in SQL Select Query or QlikView Load?

thepiapus
Contributor II
Contributor II
Author

Actually both solutions will work for me..

sunny_talwar

So, all your need is two fields after the reload finishes? [Item No_] and [Expiration Date]?

thepiapus
Contributor II
Contributor II
Author

For every record I need all values  [Item No_] , [Expiration Date] And [Lot No_]..

sunny_talwar

I am not sure I follow... can you share few rows of raw data and the output you expect to see from it?

thepiapus
Contributor II
Contributor II
Author

As you can see from sample [Item No_] has many records, I want to keep record with Min(Expiration date) 31.10.2020 in my case..

sunny_talwar

Try a Right Join after your table load

Tmp:
LOAD [Item No_], 
     [Expiration Date], 
     [Lot No_]
FROM
[C:\Users\ipohleven\Desktop\sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Right Join (Tmp)
LOAD [Item No_],
	 Min([Expiration Date]) as [Expiration Date]
Resident Tmp
Group By [Item No_];

 

thepiapus
Contributor II
Contributor II
Author

Thx, it works..