How to concatenate, join or map historic and active VAT rate?

[cdss-developer]

Hello, everybody, 

I have two tables; a table with INVOICELINES with a VAT percentage based on an H- (high) and an L- (low) indicator (column "VAT"):

INVOICE_DATE	INVOICENUMBER	INVOICELINENUMBER	ARTICLE_ID	VAT
14-06-2018	4567894	53387510	401265508	H
24-08-2018	4567895	53388875	40473597	L
10-01-2019	4567896	53388876	40782893	L
11-01-2019	4567897	53386823	401361842	H
12-01-2019	4567898	53388884	401059694	L
13-01-2019	4567899	53388885	40782893	H
14-01-2019	4567900	53386159	401232205	H
15-01-2019	4567901	53387082	401309813	H

And other one containing change history ('H' = Historic and 'A' = Active) of the VAT percentage:

VatDescr1	Value1	VatDescr2	Value2	ChangeDate	ChangeTime	Status
H	21	L	6	25-4-2014	16:42:25	H
H	21	L	9	31-12-2018	19:30:47	A

My question:
How to concatenate, join or map or ..(?).  these to get the following table including a column with the right VAT rates?

INVOICE_DATE	INVOICENUMBER	INVOICELINENUMBER	ARTICLE_ID	VAT	VAT%
14-06-2018	4567894	53387510	401265508	H	21
24-08-2018	4567895	53388875	40473597	L	6
10-01-2019	4567896	53388876	40782893	L	9
11-01-2019	4567897	53386823	401361842	H	21
12-01-2019	4567898	53388884	401059694	L	9
13-01-2019	4567899	53388885	40782893	H	21
14-01-2019	4567900	53386159	401232205	H	21
15-01-2019	4567901	53387082	401309813	H	21

Please note 24-08-2018, this line had at that time a (now expired) VAT rate of 6%.

Thank you in advance.

[marcus_sommer]

You need to create two new fields - FromDate and ToDate - from your ChangeDate. Depending on if there is only one record per ID or several ones you will need different logics to assign the appropriate values to those fields.

If there is only one record the ChangeDate = CreationDate and will be assigned to the FromDate - the ToDate could be set to today(). By multiple records it needs some more efforts because the ChangeDate from the second record will be the FromDate from the first record and so on (you might need multiple checks and assignments depending on the varity of your data and requirements). To be able to do this you need to use interrecord-functions like previous() and peek() within a properly sorted resident-load: Peek-or-Previous.

After the From/To creation you could loop - per intervalmatch or per while - over both fields to create a single date for each VAT value and the result of it could be just associated as separate table in the datamodel or you could join/map them to your Invoice table:

IntervalMatch
A-tricky-one-Allocate-Time-in-Time-periodes-Start-DateTime-End

- Marcus

[cdss-developer]

Hi Marcus,

Thank you for you're input! Although the explanation is great and it certainly does seem to be the right way to realize this, I'm still struggling making the correct data model. This (see attached) is what I've managed to do so far. 

Can you or anyone else help me any further?

Greetings,
Eelco

[marcus_sommer · Accepted solution]

Your case is slightly different to what I understood yesterday. There is no further ID involved else only the two VAT level but is has two values per change-record. Nevertheless the logic remains quite similar to my suggestion whereby the From/To assigning is easier now and it required the string-concat of the two values which are splitted again within another loop to create an appropriate KEY. In the end it might be look like:

VAT_Changes:
LOAD VatDescr1, Value1, VatDescr2, Value2, ChangeDate, ChangeTime, Status
FROM
[https://community.qlik.com/t5/New-to-QlikView/How-to-concatenate-join-or-map-historic-and-active-VAT...]
(html, codepage is 1252, embedded labels, table is @2);

Intervals:
load *, Date & '|' & Descr as MappingKey, rowno() as RowNo;
load *, subfield(ValueMatch, '|', iterno()) as Value, subfield(DescrMatch, '|', iterno()) as Descr, iterno() as IterNo2
while iterno() <= 2;
load *, date(FromDate + iterno() - 1) as Date, iterno() as IterNo1
while FromDate + iterno() - 1 <= ToDate;
Load
ChangeDate as FromDate, if(len(Previous(ChangeDate)), date(Previous(ChangeDate)-1), date(today())) as ToDate,
Value1 & '|' & Value2 as ValueMatch, VatDescr1 & '|' & VatDescr2 as DescrMatch, recno() as RecNo
resident VAT_Changes order by ChangeDate Desc;

m: mapping load MappingKey, Value resident Intervals;

Invoices:
LOAD INVOICE_DATE, INVOICE_DATE as Date, INVOICENUMBER, INVOICELINENUMBER,
ARTICLE_ID, VAT,
applymap('m', date(date#(INVOICE_DATE, 'DD-MM-YYYY')) & '|' & VAT, '#NV') as VAT_Value
FROM
[https://community.qlik.com/t5/New-to-QlikView/How-to-concatenate-join-or-map-historic-and-active-VAT...]
(html, codepage is 1252, embedded labels, table is @1);

- Marcus

[cdss-developer]

Wow Marcus, fantastic, this is exactly what I was looking for!
Thanks again for the detailed explanation.

Greetings,
Eelco