Update: Seems as if the Table Key is not showing the distinct delivery qty. Any suggestions?
Hello all, I had some help with this one a while back, but I ran the previous months data and was curious as to why this is not showing the correct data anymore. In excel, I am getting On-Time Total as 84.19, In-Full Total as 87.73 and OTIF as 75.34. Can anyone take a look at this and help me figure out why my Qlik is not displaying the correct data.
Note: the data in the qlik app is a little newer than the excel sheet attached. the decimals may be off just a bit.
Here is my script:
NoConcatenate
DataFile1:
LOAD
SaTy_OTIF & Chr(59) & Delivery_OTIF & Chr(59) & "Pl GI date_OTIF" & Chr(59) & "Ac GI date_OTIF" as "Table Key",
Material_OTIF,
Batch_OTIF,
"Delivery quantity_OTIF",
"Order Quantity_OTIF",
Delivery_OTIF,
"Pl GI date_OTIF",
"Ac GI date_OTIF",
SU_OTIF,
"Sales Doc_OTIF",
SaTy_OTIF
FROM [lib://qlikid_joshrussin/OTIF Merged Data.XLSX]
(ooxml, embedded labels, table is [sheet1]);
;
NoConcatenate
DataFile2:
Load
Sum("Delivery quantity_OTIF") as "Sum Delivery quantity_OTIF",
Sum("Order Quantity_OTIF") as "Sum Order Quantity_OTIF",
If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0, 1, 0) as "Qty IF if Positive",
If((num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as "compare AC PI Date",
If((Sum("Delivery quantity_OTIF") - Sum("Order Quantity_OTIF")) >= 0 and
(num(date([Ac GI date_OTIF]))-num(Date([Pl GI date_OTIF]))) <= 0, 1, 0) as OTIF,
SaTy_OTIF & Chr(59) & Delivery_OTIF & Chr(59) & "Pl GI date_OTIF" & Chr(59) & "Ac GI date_OTIF" as "Table Key"
Resident DataFile1
Group By SaTy_OTIF, Delivery_OTIF, "Ac GI date_OTIF", "Pl GI date_OTIF";
Drop Table DataFile1;
