topic Re: Filter data in the load script in QlikView

Filter data in the load script

thepiapus — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Filter data in the load script

sunny_talwar — Mon, 24 Feb 2020 12:57:36 GMT

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

Re: Filter data in the load script

thepiapus — Mon, 24 Feb 2020 13:00:19 GMT

Actually both solutions will work for me..

Re: Filter data in the load script

sunny_talwar — Mon, 24 Feb 2020 13:02:15 GMT

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

Re: Filter data in the load script

thepiapus — Mon, 24 Feb 2020 13:19:46 GMT

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

Re: Filter data in the load script

sunny_talwar — Mon, 24 Feb 2020 13:36:07 GMT

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

Re: Filter data in the load script

thepiapus — Mon, 24 Feb 2020 13:54:15 GMT

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

Re: Filter data in the load script

sunny_talwar — Mon, 24 Feb 2020 13:59:20 GMT

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_];

Re: Filter data in the load script

thepiapus — Mon, 24 Feb 2020 14:27:49 GMT

Thx, it works..