Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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