topic Calculated fields from multiple tables in QlikView

Calculated fields from multiple tables

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

Please be nice I'm new! so I have 2 sql load statements in my script. see examples below

sql select part_number, creation_date

from Table A;

sql select part_number, ship_date

from table B;

Then I have an expression for a graph which calculates the last receipt:

expression- Max(if(creation_date<=ship_date,creation_date))

I would like to write the last receipt calculation into my script but I don't know how to calculate it when it's pulling from 2 different tables. Please help!

Re: Calculated fields from multiple tables

vishsaggi — Mon, 27 Aug 2018 19:17:50 GMT

Do a left join like

TableA:

sql select part_number, creation_date

from Table A;

LEFT JOIN(TableA)

TableB:

sql select part_number, ship_date

from table B;

Left Join(TableA)

FinalTab:

LOAD part_number,

Max(if(creation_date<=ship_date,creation_date)) AS LastReceiptDate

Resident TableA

Group By part_number;

Re: Calculated fields from multiple tables

nburton78 — Tue, 28 Aug 2018 14:49:12 GMT

thank you! The join works perfectly but now the formula is giving me an incorrect answer. Any idea why this the formula would work perfectly as an expression but not in the script? it may have to do with specifying what instance of ship date i am referring to. If that's the case would this script work if i brought in the order number? maybe?

Re: Calculated fields from multiple tables

vishsaggi — Tue, 28 Aug 2018 15:18:14 GMT

Probably yes. I am not sure about the details so if you can provide more information on the script you are trying on or paste your script with the output you are looking for will help me to work on it.

Re: Calculated fields from multiple tables

nburton78 — Tue, 28 Aug 2018 15:38:58 GMT

Here's the script: There's quite a bit here and like I said, I'm a new user so this may not be the best way to write all this. Please don't judge me! The end result is that I want the "lastreceiptdate" field to pull the last creation date (in table b) that is less than the actual ship date in (table a).

TableA:

SQL SELECT

dbo.V_ItemBranchMaster.IBSTKT__Stocking_Type,

dbo.V_OTD_Outbound_Americas.Header_Business_Unit__SDEMCU,

dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU,

dbo.V_OTD_Outbound_Americas.Item_Classification_Detail,

dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM as [Short],

dbo.V_OTD_Outbound_Americas.IMLITM__World_Wide_Item_Number,

dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO as [Order_Number],

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO as [Order_Type],

dbo.V_OTD_Outbound_Americas.Late_Reason_Code,

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID as [Line_Number],

dbo.V_OTD_Outbound_Americas.Bill_To_Address_Number__SDAN8 as [Bill_To],

dbo.V_OTD_Outbound_Americas.Bill_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Ship_To_Address_Number__SDSHAN,

dbo.V_OTD_Outbound_Americas.Ship_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Customer_Service_Level_Bill_To,

dbo.V_OTD_Outbound_Americas.Quantity_Shipped__SDSOQS,

dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ,

dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.IBLTCM__Leadtime_Cumulative,

dbo.V_OTD_Outbound_Americas.Requested_Date_SDDRQJ,

dbo.V_OTD_Outbound_Americas.Leadtime_Date_at_SLUPMJ_Order_Date,

dbo.V_OTD_Outbound_Americas.Scheduled_Pick_Date__SDPDDJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Time__SLTDAY,

dbo.V_OTD_Outbound_Americas.Promised_Ship_Date__SDPPDJ,

dbo.V_OTD_Outbound_Americas.Promised_Delivery_Date__SDRSDJ,

dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ,

dbo.V_OTD_Outbound_Americas.Requested_On_Time,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Requested,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Ship,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Delivery,

dbo.V_OTD_Outbound_Americas.Supplier_Name,

MONTH(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Month,

YEAR(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Year,

dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN,

case when dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ=dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ

then datediff(dd,Requested_Date_SDDRQJ,Order_Date__SDTRDJ)

else datediff(dd,Requested_Date_SDDRQJ,Order_Date__SLUPMJ)

end as [Request date- order date],

case when Requested_Date_SDDRQJ<Promised_Ship_Date__SDPPDJ

then 'True'

else 'False'

end as [Request date before PSD],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Requested_Date_SDDRQJ ) as [Packslip Print-Req Date],

DATEDIFF(dd,Scheduled_Pick_Date__SDPDDJ,Requested_Date_SDDRQJ ) as [Days between scheduled pick and request],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Actual_Ship_Date__SDADDJ) as [Days between packslip print and actual ship],

DATEDIFF(dd,PickData_Transmission_Date__SLUPMJ,Scheduled_Pick_Date__SDPDDJ) as [Days between pack slip and sched pick date],

case when Line_Number__SDLNID%1000 =0

then 'Not a Split Line'

else 'Split Line'

end as [Split Line],

case when (Leadtime_Date_at_SLUPMJ_Order_Date-Order_Date__SLUPMJ)+Order_Date__SDTRDJ>Scheduled_Pick_Date__SDPDDJ

then 'Order entered inside LT'

else 'Lead Time respected'

end as [LT respected],

Requested_Date_SDDRQJ+8 as [Requested_plus_eight]

FROM dbo.V_ItemBranchMaster INNER JOIN

dbo.V_OTD_Outbound_Americas ON dbo.V_ItemBranchMaster.IBMCU__Business_Unit = dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU AND

dbo.V_ItemBranchMaster.IBITM__Item_Number_Short = dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM INNER JOIN

dbo.V_Item_Ledger_Cardex ON dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO = dbo.V_Item_Ledger_Cardex.Order_Number__ILDOCO AND

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO = dbo.V_Item_Ledger_Cardex.Order_Type__ILDCTO AND

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID = dbo.V_Item_Ledger_Cardex.Order_Line__ILLNID

WHERE (dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ >= CONVERT(DATETIME, '2013-01-01 00:00:00', 102));

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM dbo.V_Item_Ledger_Cardex

WHERE (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

load Lot_Number__ILLOTN,

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Lot_Number__ILLOTN;

Re: Calculated fields from multiple tables

vishsaggi — Tue, 28 Aug 2018 16:48:11 GMT

Your script looks absolutely fine. So the tableA leftjoin with TableB gets the LOT Number and CreationDate for those lot numbers. Then we are using the Field LotNumber to group by to get the Max creation date from those dates where creation date is <= ActualShipDate. So you said this expr works in front end so what dimension are you using? If you use LotNumber as dimension and try this expr are you not getting the right result?

Re: Calculated fields from multiple tables

nburton78 — Tue, 28 Aug 2018 17:26:57 GMT

order number and line number, these would have to be added to the join right? like this?? Table a and table b would join on lot number since its the only field they both share so do I even need the lot number?

for each distinct order number and line number combination , I need to know what the last receipt was prior to shipment.

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM dbo.V_Item_Ledger_Cardex

WHERE (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

load

Order_Number__SDDOCO ,

Line_Number__SDLNID

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Order_Number__SDDOCO ,Line_Number__SDLNID

Re: Calculated fields from multiple tables

vishsaggi — Tue, 28 Aug 2018 17:38:47 GMT

This seems convincing. So did you try this code and checked if it is picking right receiptdate?

Re: Calculated fields from multiple tables

nburton78 — Tue, 28 Aug 2018 17:49:26 GMT

I tried, there was an error on the reload. I will have to keep trying. thank you

Re: Calculated fields from multiple tables

vishsaggi — Tue, 28 Aug 2018 17:58:23 GMT

What is the error can you share a snap shot of the error?

Re: Calculated fields from multiple tables

nburton78 — Tue, 28 Aug 2018 18:17:33 GMT

it was a syntax error. I was referring to the wrong field name. It worked! Thank you for all your help!

Re: Calculated fields from multiple tables

vishsaggi — Tue, 28 Aug 2018 19:17:12 GMT

No problem.

Re: Calculated fields from multiple tables

nburton78 — Tue, 18 Sep 2018 15:09:31 GMT

If I take table A and join it with table B and create the expression lastreceiptdate, correct? is there any way to compare lastreceiptdate to other tables? like table A has a field called requested date, can I compare lastreceiptdate to requested date?

Re: Calculated fields from multiple tables

vishsaggi — Tue, 18 Sep 2018 15:11:40 GMT

Why not. Did you try that in your expression or in a chart?

Re: Calculated fields from multiple tables

nburton78 — Tue, 18 Sep 2018 15:24:10 GMT

I'd like to write it in the script. right now its an expression in the chart.

Re: Calculated fields from multiple tables

vishsaggi — Tue, 18 Sep 2018 15:39:19 GMT

I have to see the data model, are we joining this datefield to TableA or is that datefield in a separate table?

Re: Calculated fields from multiple tables

nburton78 — Tue, 18 Sep 2018 16:46:49 GMT

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWH_V1;Data Source=tssstrscm003;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TSSSMDL381;Use Encryption for Data=False;Tag with column collation when possible=False];

TableA:

SQL SELECT

dbo.V_ItemBranchMaster.IBSTKT__Stocking_Type,

dbo.V_OTD_Outbound_Americas.Header_Business_Unit__SDEMCU,

dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU,

dbo.V_OTD_Outbound_Americas.Item_Classification_Detail,

dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM as [Short],

dbo.V_OTD_Outbound_Americas.IMLITM__World_Wide_Item_Number,

dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO as [Order_Number],

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO as [Order_Type],

dbo.V_OTD_Outbound_Americas.Late_Reason_Code,

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID as [Line_Number],

dbo.V_OTD_Outbound_Americas.Bill_To_Address_Number__SDAN8 as [Bill_To],

dbo.V_OTD_Outbound_Americas.Bill_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Ship_To_Address_Number__SDSHAN,

dbo.V_OTD_Outbound_Americas.Ship_To_Customer_Name,

dbo.V_OTD_Outbound_Americas.Customer_Service_Level_Bill_To,

dbo.V_OTD_Outbound_Americas.Quantity_Shipped__SDSOQS,

dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ,

dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.IBLTCM__Leadtime_Cumulative,

dbo.V_OTD_Outbound_Americas.Requested_Date_SDDRQJ,

dbo.V_OTD_Outbound_Americas.Leadtime_Date_at_SLUPMJ_Order_Date,

dbo.V_OTD_Outbound_Americas.Scheduled_Pick_Date__SDPDDJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Date__SLUPMJ,

dbo.V_OTD_Outbound_Americas.PickData_Transmission_Time__SLTDAY,

dbo.V_OTD_Outbound_Americas.Promised_Ship_Date__SDPPDJ,

dbo.V_OTD_Outbound_Americas.Promised_Delivery_Date__SDRSDJ,

dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ,

dbo.V_OTD_Outbound_Americas.Requested_On_Time,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Requested,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Ship,

dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Delivery,

dbo.V_OTD_Outbound_Americas.Supplier_Name,

MONTH(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Month,

YEAR(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Year,

dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN,

case when dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ=dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ

then datediff(dd,Requested_Date_SDDRQJ,Order_Date__SDTRDJ)

else datediff(dd,Requested_Date_SDDRQJ,Order_Date__SLUPMJ)

end as [Request date- order date],

case when Requested_Date_SDDRQJ<Promised_Ship_Date__SDPPDJ

then 'True'

else 'False'

end as [Request date before PSD],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Requested_Date_SDDRQJ ) as [PackslipPrint-ReqDate],

DATEDIFF(dd,Scheduled_Pick_Date__SDPDDJ,Requested_Date_SDDRQJ ) as [Days_between_scheduled_pick and request],

datediff(dd,PickData_Transmission_Date__SLUPMJ,Actual_Ship_Date__SDADDJ) as [Days between packslip print and actual ship],

DATEDIFF(dd,PickData_Transmission_Date__SLUPMJ,Scheduled_Pick_Date__SDPDDJ) as [Days between pack slip and sched pick date],

case when Line_Number__SDLNID%1000 =0

then 'Not a Split Line'

else 'Split Line'

end as [Split Line],

case when (Leadtime_Date_at_SLUPMJ_Order_Date-Order_Date__SLUPMJ)+Order_Date__SDTRDJ>Scheduled_Pick_Date__SDPDDJ

then 'Order entered inside LT'

else 'Lead Time respected'

end as [LT respected],

Requested_Date_SDDRQJ+8 as [Requested_plus_eight]

FROM dbo.V_ItemBranchMaster INNER JOIN

dbo.V_OTD_Outbound_Americas ON dbo.V_ItemBranchMaster.IBMCU__Business_Unit = dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU AND

dbo.V_ItemBranchMaster.IBITM__Item_Number_Short = dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM INNER JOIN

dbo.V_Item_Ledger_Cardex ON dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO = dbo.V_Item_Ledger_Cardex.Order_Number__ILDOCO AND

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO = dbo.V_Item_Ledger_Cardex.Order_Type__ILDCTO AND

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID = dbo.V_Item_Ledger_Cardex.Order_Line__ILLNID

WHERE (dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ >= CONVERT(DATETIME, '2013-01-01 00:00:00', 102));

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM dbo.V_Item_Ledger_Cardex

WHERE (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

load

Order_Number,

Order_Type,

Line_Number,

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Order_Number, Order_Type, Line_Number;

sql SELECT ABAN8__Address_Number as [Bill_To], AIINMG__Print_Message as [LG_NO_Early_Ship]

FROM dbo.V_AddressBookMaster

WHERE AIINMG__Print_Message IN ('CN83642',

'CNMESSIE1',

'CRT-MFCOCN',

'CS100870',

'CS101278',

'CS102266',

'CS102502',

'CS103327',

'CS104673',

'CS104846',

'CS104856',

'CS104857',

'CS1055',

'CS105531',

'CS105541',

'CS105542',

'CS105548',

'CS105549',

'CS105550',

'CS106892',

'CS107473',

'CS109383',

'CS111588',

'CS115033',

'CS115741',

'CS1158',

'CS116029',

'CS116089',

'CS116100',

'CS116547',

'CS116889',

'CS14255',

'CS14845',

'CS15624',

'CS16115',

'CS17664',

'CS17735',

'CS17939',

'CS1852',

'CS2100',

'CS22403',

'CS22678',

'CS22706',

'CS22745',

'CS25144',

'CS2624',

'CS2895',

'CS3357',

'CS3476',

'CS38864',

'CS4136',

'CS81014',

'CS84091',

'CS84232',

'CS84661',

'CS84672',

'CS86550',

'CS87188',

'CS9586',

'CS99550',

'DS17510',

'DS17925',

'DS20285',

'DS20286',

'DSCLARK',

'DSGD',

'DSJD',

'DSNE',

'ELC01',

'FW22937',

'FW84898',

'FW86972',

'FWARKWIN',

'FWPARKERAB',

'FWROTEM',

'GABUE1',

'GL20715',

'LG101660',

'LG106790',

'LG17783',

'LG21870',

'LG2841',

'LG3290',

'LG84218',

'LG84232',

'LGASNNE',

'LGGMSPO',

'LGNOEARLY',

'LGNOEARLYD',

'LGSKF00001',

'STW00008',

'STW00009',

'STW00103',

'VRF-DOCKNE',

'WEB-NOTRAC',

'WHMAN',

'WOODW11779'

);

sql SELECT distinct IL1.Lot_Number__ILLOTN,

CASE WHEN IL1.Document_Type__ILDCT = 'OV' THEN NULL ELSE IL2.Lot_Number__ILLOTN END AS [LL Lot],

CASE WHEN IL1.Document_Type__ILDCT = 'OV' THEN NULL ELSE IL3.Creation_Date__ILCRDJ END AS [LL Creation]

FROM dbo.V_Item_Ledger_Cardex AS IL1 LEFT OUTER JOIN

dbo.V_Item_Ledger_Cardex AS IL2 ON IL1.Document_Number__ILDOC = IL2.Document_Number__ILDOC LEFT OUTER JOIN

dbo.V_Item_Ledger_Cardex AS IL3 ON IL2.Lot_Number__ILLOTN = IL3.Lot_Number__ILLOTN

WHERE (IL1.Explanation__ILTREX <> 'Landed Cost') AND (IL1.Document_Type__ILDCT IN ( 'IC')) AND (IL1.Order_Type__ILDCTO <> 'OX') AND

(IL2.Document_Type__ILDCT IN ('IM', 'OV')) AND (IL3.Document_Type__ILDCT IN ('OV', 'IC')) AND (IL3.Explanation__ILTREX <> 'Landed Cost') AND

(IL1.Lot_Number__ILLOTN IN

(SELECT dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN

FROM dbo.V_OTD_Outbound_Americas INNER JOIN

dbo.V_Item_Ledger_Cardex ON dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO = dbo.V_Item_Ledger_Cardex.Order_Number__ILDOCO AND

dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO = dbo.V_Item_Ledger_Cardex.Order_Type__ILDCTO AND

dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID = dbo.V_Item_Ledger_Cardex.Order_Line__ILLNID));

sql SELECT [IBMCU__Business_Unit]

,[IBITM__Item_Number_Short]as [Short]

,[IBLITM__World_Wide_Item_Number]

,[Item_Classification]

,[IBSAFE__Safety_Stock_Qty]

FROM [DWH_V1].[dbo].[V_ItemBranchMaster]

WHERE IBMCU__Business_Unit = 'LG01';

LOAD [Causer Main Group],

[Late Reason Causer],

Late_Reason_Code,

Late_Reason_Code_Description

FROM

(ooxml, embedded labels, table is LRC_Detail_Text);

Directory;

LOAD CONCAT, [Start Date (F554311A)], [Complete Date (F554311A)], [Primary Supplier (F554311A)],

[Short Item No (F554311B)]as [Short],

[2nd Item Number (F554311B)],

[Order Number (F554311A)] as [Order_Number],

[Or Ty (F554311A)] as [Order_Type],

[Line Number (F554311A)],

Line_Number_K as [Line_Number],

[Count of PR]as [Count_of_PR]

FROM [PR Report.xlsx] (ooxml, embedded labels, table is Sheet1) WHERE(CONCAT <> '0');

Directory;

LOAD Order_Number__SDDOCO as [Order_Number],

Order_Type__SDDCTO as [Order_Type],

Line_Number__SDLNID as [Line_Number],

Group as [Scotts_Group],

Detail as [Scotts_Detail]

FROM [combined 2.xlsx] (ooxml, embedded labels, table is [Combine Sheet]);

Re: Calculated fields from multiple tables

nburton78 — Tue, 18 Sep 2018 16:54:42 GMT

So I joined table A to table B to create the expression LastReceiptDate. Now I want to compare Lastreceiptdate to other tables in table A. Can I do that? like

case when (lastreceiptdate>Requested_Date_SDDRQJ

then ''Inventory not available'

else 'inventory avaiable'

end as [Inventory available],