Solved: Lookup transaction data by key and date out of mas... - Qlik Community

Report Inappropriate Content · ‎2010-09-09

Hallo everybody,

I'm a beginner with QlickView and I'm facing following issue.

I have a master data table with information which is based on a "valid from" date and I've got transaction details with dates.

So I have to look up the valid master information based on transaction date.

Here the example

Master data table

Key valid from date Information (surcharge)

A 01.01.1900 1.5

B 10.08.2010 1.8

B 02.03.2010 1.3

B 01.01.1900 1.6

C 07.07.2009 3.7

C 01.01.1900 2.5

D …

Transaction data

Key Date …..

B 05.05.2010

C 10.10.2009

A 03.02.2008

So I'm able to look up the information with the lookup function. But I don't find a way to check the date.

(e.g. for the first transaction row B 05.05.2010 >> Lookup key B it shoes 1.8 instead of 1.3).

I would need something like this:

For i = 1 to count(Key)

Find Key

Row = Find.Key.Row

If (TransactionDate < Value(Row, ValidFromDate) then

Row = Row +1

Else

Value(Row, Information)

End if

Next i

Has someone got a solution? Please help me. Thank you very much.

Don't stay with my presumption - I'm a beginner and I don't know it better!

Regards

John D.

disqr_rm · ‎2010-09-09

Many possibilities out there, but what I would do is create a range.

So I would add another field in master table, say ValidToDate and initialize it to 12/31/2999. Then I would do intervalmatch() function passing Key as parameter. Something link this

Master:

Key, ValidFrom, surcharge

Transaction:

Key, Date

Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

Intervalmatch(Date, Key)

LOAD ValidFrom, ValidTo, Key Resident Master;

////

And if you do not like synthetic keys, you can left join master table contents back to transactions by using left join. Like:

Master:

Key, ValidFrom, surcharge

Transaction:

Key, Date

Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

Left join (Transaction)

Intervalmatch(Date, Key)

LOAD ValidFrom, ValidTo, Key Resident Master;

Left join (Transaction)

LOAD * RESIDENT Master;

DROP Table Master.

////

Hope this gives you some ideas & helps you a little.

View solution in original post

disqr_rm · ‎2010-09-09

Many possibilities out there, but what I would do is create a range.

So I would add another field in master table, say ValidToDate and initialize it to 12/31/2999. Then I would do intervalmatch() function passing Key as parameter. Something link this

Master:

Key, ValidFrom, surcharge

Transaction:

Key, Date

Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

Intervalmatch(Date, Key)

LOAD ValidFrom, ValidTo, Key Resident Master;

////

And if you do not like synthetic keys, you can left join master table contents back to transactions by using left join. Like:

Master:

Key, ValidFrom, surcharge

Transaction:

Key, Date

Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

Left join (Transaction)

Intervalmatch(Date, Key)

LOAD ValidFrom, ValidTo, Key Resident Master;

Left join (Transaction)

LOAD * RESIDENT Master;

DROP Table Master.

////

Hope this gives you some ideas & helps you a little.

Report Inappropriate Content · ‎2010-09-09

Normal 0 21 false false false DE ZH-TW X-NONE MicrosoftInternetExplorer4

Hi Rakesh,

thanks a lot for your quick and good answer!!

Sorry that it took me so long to answer.

You really gave me the right ideas and after I work a little bit with it, I was able to solve my issue.

Have a nice day

John D.

Lookup transaction data by key and date out of master data