Sap Eket and Ekes table for Delivery Performance

[QSense]

Hello , 

I have a problem in writing query in qlik. In my case ; 

there are two tables called EKET and EKES. 

Eket table includes our agreement with vendor about quantity and time. In my case Total quantity is 30 and deadline is wrtten in EKET.

But EKES table includes vendor's performance. According to sample. First line item with quantity 10 is late. 10 quantity is delivered in 05.10.2019 but our agreeement is 01.10.2019. so 4 days late.

Second item is deadline with quantity is 09.10.2019 but vendor completed order in 22.10.2019. 11 days is late.

hoe can i calculate for each agreement's performance. ?

EKET : 

EBELN   |  EBELP  | LINE_ITEM | Quantity | DATE

100                  0001                    01              10       01.10.2019(dd.mm.yyyy)

100                  0001                    02              20       09.10.2019

EKES:

EBELN   |  EBELP  | DELIVERY_ITEM | Quantity | DATE

100                  0001                    01                        1          01.10.2019

100                  0001                    02                        7          03.10.2019

100                  0001                    03                        2          05.10.2019

100                  0001                    04                        10         09.10.2019

100                  0001                    05                        5          21.10.2019

100                  0001                    06                        5          22.10.2019

[lfetensini]

Hello Friend. If I understand your problem, you want to perform functions between the two tables in a more performative way.

The trick is to add a deadline end date to the Deadline table and then create some mappings.

I'll take this question to show you some cool possibilities.

See in "Result.png" if corresponds to your question.

/*
We need specify a START and END date for each LINE_ITEM. If dont have one, must be created.

I will assume the Month End in last sentence that dont have date, otherwise use Today()
*/

TMP_Dates_1:
LOAD
EBELN,
EBELP,
Quantity as Deadline_Qtd,
LINE_ITEM as Deadline_Num,
Date(Date#(DATE, 'DD.MM.YYYY')) as Deadline_Date_Start
FROM [D:\Documentos\Desktop\Teste Qlik\Book1.xlsx](ooxml, embedded labels, table is EKET);

Left Join(TMP_Dates_1)
LOAD
*,
If(
Date( Peek('Deadline_Date_Start')-1 ) <> Null(),
Date( Peek('Deadline_Date_Start')-1 ),
Date(MonthEnd(Deadline_Date_Start))) as Deadline_Date_End
Resident TMP_Dates_1
Order By Deadline_Date_Start desc;

// Now we need a range with START/END dates and Total Qtd by Deadline number to future ApplyMap
TMP_Dates_2:
LOAD
EBELN,
EBELP,
Text( Date( Deadline_Date_Start + IterNo() - 1, 'DD.MM.YYYY' ) ) as Date, // to nature table adaptation
Deadline_Num,
Deadline_Qtd
// Deadline_Date_Start,
// Deadline_Date_End
Resident TMP_Dates_1
While IterNo() <= Deadline_Date_End - Deadline_Date_Start +1;

Drop Table TMP_Dates_1;

// Our mapping with some SubField() techniques:
MAP_Deadline:
Mapping LOAD
EBELN &'|'& EBELP &'|'& Date as MAP_Deadline_1,
Deadline_Num &'|'& Deadline_Qtd as MAP_Deadline_2
Resident TMP_Dates_2;

Drop Table TMP_Dates_2;

// Now the magic happens:

My_Table:
LOAD
 EBELN,
 EBELP, 
 Num#(DELIVERY_ITEM) as DELIVERY_ITEM,
 Num#(Quantity) as Quantity,
 Date(Date#(DATE, 'DD.MM.YYYY')) as Date,
 SubField(ApplyMap('MAP_Deadline', EBELN &'|'& EBELP &'|'& DATE),'|',1) as Deadline_Num,
 //SubField(ApplyMap('MAP_Deadline', EBELN &'|'& EBELP &'|'& DATE),'|',2) as Deadline_Qtd,
 Quantity / SubField(ApplyMap('MAP_Deadline', EBELN &'|'& EBELP &'|'& DATE),'|',2) as Deadline_Percentage
FROM [D:\Documentos\Desktop\Teste Qlik\Book1.xlsx](ooxml, embedded labels, table is EKES);