Announcements
cancel
Showing results for
Did you mean:
Contributor III

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

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%.

Labels (4)

• ### VAT

1 Solution

Accepted Solutions

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;
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

4 Replies

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:

- Marcus

Contributor III
Author

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

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;
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

Contributor III
Author

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

Greetings,
Eelco

Community Browser