topic Re: Left Join from one qvd to another doesn't work in QlikView

Left Join from one qvd to another doesn't work

uba_ha — Sat, 16 Nov 2024 02:35:52 GMT

Hello experts, I just got my hands on Qlik. I tried to perform some left joins to update ("Payment1.qvd") to the historical qvd ("Original1.qvd") but it seems it didn't capture the update onto the historical qvd.

I used the [Invoice ID] and [Supplier INV No.1] as the key for the join up. I checked I didn't generate any synthetic keys to confuse the script so I am not sure why. Please see the attached qvw.

Any ideas would be much appreciated. 🙂

Re: Left Join from one qvd to another doesn't work

martinpohl — Fri, 30 Aug 2019 11:47:40 GMT

Hi,

I don't understand what you are trying to do.

All values your are loading in left join load - statemennt are existing field in table Joinup.

So you won't get any addintional rows are fields.

Qlik is joining table in a join load statement within the same existing ao renamed fields, unequal field will be created in the table.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/Join.htm

Regards

Re: Left Join from one qvd to another doesn't work

uba_ha — Fri, 30 Aug 2019 12:56:24 GMT

hi I am trying to update the value on the field "Supplier Invoice Paid?" and "Supplier_Invoice_Paid_Date" from blank to "paid" and the date using the specific "Invoice ID" of "S584".

Re: Left Join from one qvd to another doesn't work

agigliotti — Fri, 30 Aug 2019 13:08:03 GMT

you have to remove the following fields from Original1.qvd.

[Supplier Invoice Paid?]
[Supplier_Invoice_Paid_Date]

i hope it helps.

Re: Left Join from one qvd to another doesn't work

uba_ha — Fri, 30 Aug 2019 16:29:30 GMT

Hi, what I am trying to do is to update the values on the existing fields, i.e. "Supplier Invoice Paid?" and "Supplier_Invoice_Paid_Date".

Let me explain using below screenshots. The below is the extract of the "original1" qvd where S474 has been paid in the historical record and shouldn't change in future. It is just for viewing.

Supplier INV No.1	Invoice ID	Supplier Invoice Paid?	Supplier_Invoice_Paid_Date
S474	17988	Paid	02/07/2019
S474	18051	Paid	02/07/2019
S474	18052	Paid	02/07/2019
S474	18053	Paid	02/07/2019
S474	18054	Paid	02/07/2019
S474	18058	Paid	02/07/2019
S584	22267
S584	22136
S584	22431
S584	22430
S584	22418
S584	22230
S584	22193

The below is the qvd I want to join in so that S584 would be marked as paid.

Supplier INV No.1	Invoice ID	Supplier Invoice Paid?	Supplier_Invoice_Paid_Date
S584	22267	Paid	29/08/2019
S584	22136	Paid	29/08/2019
S584	22431	Paid	29/08/2019
S584	22430	Paid	29/08/2019
S584	22418	Paid	29/08/2019
S584	22230	Paid	29/08/2019
S584	22193	Paid	29/08/2019

If I remove the following fields from Original1.qvd, then I lose the historical record for other invoice id.

[Supplier Invoice Paid?]
[Supplier_Invoice_Paid_Date]

Hope that makes sense 🙂

Re: Left Join from one qvd to another doesn't work

agigliotti — Fri, 30 Aug 2019 17:07:49 GMT

ok then you should rename them using an alias as below:

LOAD Supplier,
[Supplier INV No.1],
[INV Date],
[Invoice ID],
Worker,
Total,
[Margin Pass Back Total (Net)],
Today,
[Due Date],
Overdue?,
[Supplier Invoice Paid?] as [Supplier Invoice Paid? orig] ,
[Supplier_Invoice_Paid_Date] as [Supplier_Invoice_Paid_Date orig] ,
Comments
FROM
[H:\Qlikview Training\Margin Passback\Mock\Original1.qvd](qvd);

Re: Left Join from one qvd to another doesn't work

rwunderlich — Fri, 30 Aug 2019 20:11:08 GMT

Load Payments first -- updates followed by existing -- and then join payments to the other details:

Master:
Load
[Invoice ID],
[Supplier Invoice Paid?],
Supplier_Invoice_Paid_Date
from
[H:\Qlikview Training\Margin Passback\Mock\Payment1.qvd](qvd);

Concatenate (Master)
LOAD
[Invoice ID],
[Supplier Invoice Paid?],
[Supplier_Invoice_Paid_Date]
FROM
[H:\Qlikview Training\Margin Passback\Mock\Original1.qvd](qvd)
Where not Exists([Invoice ID])
;

Join (Master)
LOAD Supplier,
[Supplier INV No.1],
[INV Date],
[Invoice ID],
Worker,
Total,
[Margin Pass Back Total (Net)],
Today,
[Due Date],
Overdue?,
Comments
FROM
[H:\Qlikview Training\Margin Passback\Mock\Original1.qvd](qvd);

Store Master into Join_up1.qvd (qvd);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Left Join from one qvd to another doesn't work

uba_ha — Mon, 02 Sep 2019 09:19:08 GMT

thank you Rwunderlich and others for the help!!

much appreciated:)