Excel equivalent vLookup not working correctly

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

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;

topic Excel equivalent vLookup not working correctly in QlikView

Excel equivalent vLookup not working correctly