Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
New Contributor II

Left Join from one qvd to another doesn't work

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. 🙂

Labels (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

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

 

View solution in original post

7 Replies
Highlighted
Partner
Partner

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

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/Scrip...

Regards

Highlighted
New Contributor II

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

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".

Highlighted
Partner
Partner

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

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

[Supplier Invoice Paid?]
[Supplier_Invoice_Paid_Date]

i hope it helps.

Highlighted
New Contributor II

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

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.1Invoice IDSupplier Invoice Paid?Supplier_Invoice_Paid_Date
S47417988Paid02/07/2019
S47418051Paid02/07/2019
S47418052Paid02/07/2019
S47418053Paid02/07/2019
S47418054Paid02/07/2019
S47418058Paid02/07/2019
S58422267  
S58422136  
S58422431  
S58422430  
S58422418  
S58422230  
S58422193  

 

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

Supplier INV No.1Invoice IDSupplier Invoice Paid?Supplier_Invoice_Paid_Date
S58422267Paid29/08/2019
S58422136Paid29/08/2019
S58422431Paid29/08/2019
S58422430Paid29/08/2019
S58422418Paid29/08/2019
S58422230Paid29/08/2019
S58422193Paid29/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 🙂

Highlighted
Partner
Partner

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

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);

Highlighted
MVP & Luminary
MVP & Luminary

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

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

 

View solution in original post

Highlighted
New Contributor II

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

thank you 

much appreciated:)