topic Re: Sorting order in Qlik sense in App Development

Sorting order in Qlik sense

Sush123 — Tue, 22 Mar 2022 11:52:04 GMT

Hi All,

Need a quick help on the below data.

I have the table with below set .The data is sorted by Customer, Prod_Code, Date and the comparison happens between current date and previous date. If the Prod_Code is there in the previous week then the Type will be Old , other wise New.

And rowno 2 is the repeat of rowno1 , hence the expected Type should be New. But it is coming as Old .Below is my code.

Can anyone suggest if anything missing here so that the values not coming properly.

Tab1:
Load
Customer, Prod_Code,Date, Receipient Id, Type
From file
Order by Customer, Prod_Code,Date, Receipient Id, Type;

Tab2:
Load *,
If( Customer=Peek(Customer) and Prod_Code=Peek(Prod_Code) and Date=Peek(Date)
and Peek(Type)='New', New',Type) as [Expected Type]
Resident Tab1;

Drop table Tab1;

Customer	Prod_Code	Date	Receipient Id	Type	Expected Type
W32547	GEN-012	01-06-2021	12211	New	New
W32547	GEN-012	01-06-2021	12212	Old	New
W32547	GEN-013	01-06-2021	12213	New	New
W32547	GEN-013	08-06-2021	12234	Old	Old
W32547	GEN-015	08-06-2021	12235	New	New
W32547	GEN-012	30-08-2021	54345	New	New
W32547	GEN-013	30-08-2021	54355	Old	Old

Re: Sorting order in Qlik sense

hic — Tue, 22 Mar 2022 12:44:29 GMT

You cannot use an "order by" together with a "Load ...from". You must use a "Load ...resident".

Try

Tab1:
Load ... From file ;

Tab2:
Load *,
If( Customer=Peek(Customer) and Prod_Code=Peek(Prod_Code) and Date=Peek(Date)
and Peek(Type)='New', New',Type) as [Expected Type]
Resident Tab1
Order by Customer, Prod_Code,Date, Receipient Id, Type;

Drop table Tab1;

Re: Sorting order in Qlik sense

Sush123 — Tue, 22 Mar 2022 13:42:48 GMT

Hi @hic ,

I have implemented in the same way. But it is not giving the expected result for the column [Expected Type].

Tab1:
Load ... From file ;

Tab2:
Load *,
If( Customer=Peek(Customer) and Prod_Code=Peek(Prod_Code) and Date=Peek(Date)
and Peek(Type)='New', New',Type) as [Expected Type];

Load *,
If( Customer=Peek(Customer) and Prod_Code=Peek(Prod_Code),'Old', 'New') as [Type];
Resident Tab1
Order by Customer, Prod_Code,Date, Receipient Id, Type;

Drop table Tab1;