Filter data in the load script

[thepiapus]

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

[sunny_talwar]

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

[thepiapus]

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]

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]

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 · Accepted solution]

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]

Thx, it works..