Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 🙂
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
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.
Regards
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".
you have to remove the following fields from Original1.qvd.
[Supplier Invoice Paid?]
[Supplier_Invoice_Paid_Date]
i hope it helps.
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 🙂
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);
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
thank you Rwunderlich and others for the help!!
much appreciated:)