Join and Group

Tue, 08 Dec 2009 22:12:17 GMT

I have a application that contains undelivered customer orders grouped by article no and warehouse.

I would like to join in the purchase order with the nearest delivery date and its confirmation type (D-Def, P- Prel, X- Not confirmed).

I have managed to get the lowest date but cant get the confirmation type.

Problem is that INKÖPSSTOCK2.QVD can contain to Purchas orders with the same OJBKLT. Then I want only the one with lowest OJDAT1 (created date).

OJBKLT has format YYYYWWD that converts to date YYYYMMDD in the end.

The result should be like:

Artno_warehouseno, Low_conf_date, Confimationtype

Ex: 100-01, 2009-12-08, D

Beräknadinleverans_tmp:
LOAD
Artnr&'-'&OJLST as Artno_warehouseno,
Artnr as Articleno,
OJLST as warehouseno,
min(OJBKLT) as Low_conf_date
FROM (qvd)
group by Artnr, OJLST;

Left join (Beräknadinleverans_tmp)
LOAD
Artnr&'-'&OJLST as Artno_warehouseno,
OJBKLT as Low_conf_date,
Confirmationtype
FROM (qvd);

Beräknadinleverans_tmp2:
LOAD
Artno_warehouseno,
OJAID,
OJLST,
Low_conf_date,
left(Low_conf_date,4) as Year,
mid(Low_conf_date,5,2) as Vecka,
Right(Low_conf_date,1) as Dag,
Confirmationtype
Resident Beräknadinleverans_tmp;

Drop table Beräknadinleverans_tmp;

Beräknadinleverans:
LEFT JOIN (Restorders)
LOAD
Artno_warehouseno,
OJAID,
OJLST,
MakeWeekDate(År, Vecka, Dag) as Low_conf_date,
Confirmationtype
Resident Beräknadinleverans_tmp2;

drop table Beräknadinleverans_tmp2;

topic Join and Group in QlikView

Join and Group