Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator III

Excel equivalent vLookup not working correctly

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;

0 Replies