topic Re: Select Query in QlikView

Select Query

farolito20 — Thu, 27 Dec 2012 15:12:41 GMT

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?

Re: Select Query

RickWild64 — Thu, 27 Dec 2012 15:32:37 GMT

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)

Re: Select Query

RickWild64 — Thu, 27 Dec 2012 15:53:50 GMT

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;

Re: Select Query

farolito20 — Thu, 27 Dec 2012 16:23:32 GMT

How show just sales, in the left table of sales?

Re: Select Query

RickWild64 — Thu, 27 Dec 2012 16:46:40 GMT

Re: Select Query

farolito20 — Thu, 27 Dec 2012 16:56:35 GMT

Thanks