Sort Products by Value in Script

michael_maeuser — Tue, 31 Jan 2012 11:14:18 GMT

Hi,

I´m trying to Join the table (see script below) Lagerwert into table Salden and sort by the field Value[desc]. Somehow it seems that I do it wrong.

Does anyone know how to do it?

Salden:

Load
%Product,
Date,
"Location Code" as Lagerort,
[QuantityproTag alle Tage],
If(IsNull(Peek([QuantityproTag alle Tage])),[QuantityproTag alle Tage],
If(%SaldenArtikel=Previous(%SaldenArtikel) and "Location Code"=Previous("Location Code"),Peek([Täglicher Saldo])+[QuantityproTag alle Tage],[QuantityproTag alle Tage])) as [Täglicher Saldo]
;
Load
Productas %Product,
Date,
"Location Code",
Applymap('mapValue',Product&"Location Code"&Date,0) as [QuantityproTag alle Tage]
Resident TempJoin
Order By SaldenArtikel, "Location Code",SaldenDatum;

Drop table TempResident, TempJoin;

QUALIFY

*;
UNQUALIFY %Product, SaldoDatum, Date, Einstandspreis, Value, Lagerort, Lagerortcode;

//Ermittlung LagerwertHeute
BestandTemp:
LOAD
%Product,
Lagerort as Lagerortcode,
sum(if(SaldoDatum=Today(),[Täglicher Saldo])) as BestandHeute,
SaldoDatum as Datum
Resident Salden
Group By %SaldenArtikel, Lagerort, SaldoDatum,[Täglicher Saldo];

Lagerartikel:
left join (BestandTemp)
LOAD "No_" as %Product,
"Unit Cost" as Einstandspreis;
SQL SELECT
"No_",
"Unit Cost",
Description
FROM "QLIK ProBatch300 2009R2 DE DEV".dbo."DEVEL Pro Batch 3_00 DE$Item";

Lagerwert:
LOAD
%Product,
Lagerortcode,
BestandTemp.BestandHeute*Einstandspreis as Value
Resident BestandTemp
Order by %Product, Lagerortcode;

DROP Table BestandTemp;

//Ermittlung LagerwertHeute Ende

Sort Products by Value in Script

swuehl — Tue, 31 Jan 2012 13:34:47 GMT

I assume that you want to sort your last loaded table Lagerwert, sorted by Value desc.

Value is not part of the resident input table, BestandTemp, so you can't use Value as a order by field name.

If you really need to order your table Lagerwert, you probably need another resident sorted load.

topic Sort Products by Value in Script in QlikView

Sort Products by Value in Script

Sort Products by Value in Script