Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
cdss-developer
New 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_DATEINVOICENUMBERINVOICELINENUMBERARTICLE_IDVAT
14-06-2018456789453387510401265508H
24-08-201845678955338887540473597L
10-01-201945678965338887640782893L
11-01-2019456789753386823401361842H
12-01-2019456789853388884401059694L
13-01-201945678995338888540782893H
14-01-2019456790053386159401232205H
15-01-2019456790153387082401309813H

 

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

VatDescr1Value1VatDescr2Value2ChangeDateChangeTimeStatus
H21L625-4-201416:42:25H
H21L931-12-201819:30:47A


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

INVOICE_DATEINVOICENUMBERINVOICELINENUMBERARTICLE_IDVATVAT%
14-06-2018456789453387510401265508H21
24-08-201845678955338887540473597L6
10-01-201945678965338887640782893L9
11-01-2019456789753386823401361842H21
12-01-2019456789853388884401059694L9
13-01-201945678995338888540782893H21
14-01-2019456790053386159401232205H21
15-01-2019456790153387082401309813H21

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

Thank you in advance.

Labels (4)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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

4 Replies
MVP & Luminary
MVP & Luminary

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

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
New Contributor III

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

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

MVP & Luminary
MVP & Luminary

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

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
New Contributor III

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

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

Greetings,
Eelco