Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this first table:
Customer:
ID, Name, Currency, ValidFrom, ValidTill
001, Juan, USD, 25/06/2001, 31/12/2009
001, Juan COR, 01/01/2010, 27/12/2012
This is the other table:
Sales:
ID_Sales, Sales, ID_Customer, Date
1524 , 500 , 001 , 30/07/2008
I need to know the currency of sale 1524. I supose that is USD, by date, but QlikView shows both until I select a date range.
Can not be displayed directly without selecting a date range?
OK, here's the quick way of doing it:
Customer:
LOAD ID as ID_Customer,
Name,
Currency,
ValidFrom,
ValidTill
FROM
C:\_QlikViewApplications\IntervalMatch\customer.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Sales:
LOAD ID_Sales,
Sales,
ID_Customer,
Date
FROM
C:\_QlikViewApplications\IntervalMatch\sales.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
left join (Sales)
Link:
IntervalMatch(Date, ID_Customer)
load ValidFrom,
ValidTill,
ID_Customer
resident Customer
;
This is based on copy and pasting your data samples into text files. Replace the From clauses with your own data sources.
You are left with a synthetic key on ID_Customer + ValidFrom + ValidTill. You can avoid the synthetic key if you change the code a little:
Customer:
LOAD
ID&'|'&ValidFrom&'|'&ValidTill as CustomerEffDateKey,
ID as ID_Customer,
Name,
Currency,
ValidFrom,
ValidTill
FROM
C:\_QlikViewApplications\IntervalMatch\customer.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Sales:
LOAD ID_Sales,
Sales,
ID_Customer,
Date
FROM
C:\_QlikViewApplications\IntervalMatch\sales.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
left join (Sales)
IntervalMatch(Date, ID_Customer)
load
ValidFrom,
ValidTill,
ID_Customer
resident Customer
;
left join (Sales)
load
ID_Customer&'|'&ValidFrom&'|'&ValidTill as CustomerEffDateKey,
ID_Sales,
Sales,
Date
resident Sales
;
drop field ID_Customer, ValidFrom, ValidTill from Sales;
Hi,
It's not clear how your tables are associated. You suppose the currency of the sale is USD because of the date, but QlikView doesn't know that.
The problem is that currency is an attribute of the customer and not of the sale. Assuming the tables are associated by ID_Customer, if you select sale 1524, that only selects customer 001, for which you have multiple rows, sp you see both currencies.
It would be usual in a Sales Order Processing system to have a default currency as an attribute of the cusstomer, and an actual currency as an attribute of the sale. If your system really does not have a Sale_Currency column, you need either to create one in the script, or associate sale with customer using a compound key that includes the date part.
Either way, in QlikView terms you want to use Intervalmatch. (just working out the exact syntax - I'll post it in a minute)
OK, here's the quick way of doing it:
Customer:
LOAD ID as ID_Customer,
Name,
Currency,
ValidFrom,
ValidTill
FROM
C:\_QlikViewApplications\IntervalMatch\customer.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Sales:
LOAD ID_Sales,
Sales,
ID_Customer,
Date
FROM
C:\_QlikViewApplications\IntervalMatch\sales.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
left join (Sales)
Link:
IntervalMatch(Date, ID_Customer)
load ValidFrom,
ValidTill,
ID_Customer
resident Customer
;
This is based on copy and pasting your data samples into text files. Replace the From clauses with your own data sources.
You are left with a synthetic key on ID_Customer + ValidFrom + ValidTill. You can avoid the synthetic key if you change the code a little:
Customer:
LOAD
ID&'|'&ValidFrom&'|'&ValidTill as CustomerEffDateKey,
ID as ID_Customer,
Name,
Currency,
ValidFrom,
ValidTill
FROM
C:\_QlikViewApplications\IntervalMatch\customer.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
Sales:
LOAD ID_Sales,
Sales,
ID_Customer,
Date
FROM
C:\_QlikViewApplications\IntervalMatch\sales.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);
left join (Sales)
IntervalMatch(Date, ID_Customer)
load
ValidFrom,
ValidTill,
ID_Customer
resident Customer
;
left join (Sales)
load
ID_Customer&'|'&ValidFrom&'|'&ValidTill as CustomerEffDateKey,
ID_Sales,
Sales,
Date
resident Sales
;
drop field ID_Customer, ValidFrom, ValidTill from Sales;
How show just sales, in the left table of sales?
Thanks