Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Sebastian_Dec
Creator II
Creator II

Data row-1

Hi,

I have a sorted table and I want to transform it. This is a table with a price list that only has a Data_Price_from and I need to add the end date of the price list (Data_Price_to).

The relationship is as follows:

If there is the same value for ArtId and Dossier in the first and second row, the "price to" will be the date from the second row munus one day.
If the values in the first and second lines are different, the "price to" is the current day (date of generating the report).
For the second row you take the data from the second and third row,
For the third row, you take data from the third and fourth rows

What I have:

ArtId Dossier Data_Price_from RowNo
1002 1000 01.01.2018 1
1002 1000 17.10.2018 2
1002 1000 12.09.2020 3
1002 1000 26.03.2021 4
1003 1000 23.10.2018 5
1003 1000 12.09.2020 6
1003 1000 26.03.2021 7
1004 1000 23.10.2018 8
1004 1000 12.09.2020 9
1004 1000 26.03.2021 10
1006 1000 23.10.2018 11
1020 808152 10.01.2022 12
1020 808371 14.01.2022 13
1020 810664 08.03.2022 14
1020 810916 15.03.2022 15
1016 819846 18.10.2022 16
1016 819874 18.10.2022 17

 

 

What I need:

ArtId Dossier Data_Price_from RowNo Data_Price_to
1002 1000 01.01.2018 1 16.10.2018
1002 1000 17.10.2018 2 11.09.2020
1002 1000 12.09.2020 3 25.03.2021
1002 1000 26.03.2021 4 21.11.2023
1003 1000 23.10.2018 5 11.09.2020
1003 1000 12.09.2020 6 25.03.2021
1003 1000 26.03.2021 7 21.11.2023
1004 1000 23.10.2018 8 11.09.2020
1004 1000 12.09.2020 9 25.03.2021
1004 1000 26.03.2021 10 21.11.2023
1006 1000 23.10.2018 11 21.11.2023
1020 808152 10.01.2022 12 13.01.2022
1020 808371 14.01.2022 13 07.03.2022
1020 810664 08.03.2022 14 14.03.2022
1020 810916 15.03.2022 15 21.11.2023
1016 819846 18.10.2022 16 17.10.2022
1016 819874 18.10.2022 17 21.11.2023

 

Please help me.

My qlik code:

 

Price:
LOAD
    ArtId,
    Dossier,
    Data_Price_from,
    RowNo,
    Price_100,
    Price_buy,    
    Price_net,
    Promo_%
FROM [lib://QlikContainers\price.qvd]
(qvd)
;

 

 

I was thinking about something like this, but I don't know how to implement it:
If (ArtId&Dossier =
ArtId&Dossier_ROW-1, Date(Data_Price_from-1), date(today()))

 

Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.
Labels (4)
3 Solutions

Accepted Solutions
Jebrezov
Contributor III
Contributor III

There is a peek() function in Qlik which allows the user to view a prior record already loaded. this means on the load for this example, we need to sort things in descending order based on the RowNo. It is a big assumption that the Row number is assigned from the source after being sorted by ArtId, Dossier, and Data_Price_from fields in ascending order. If that is not the case, this solution will likely need to be modified.

Below is what i think gets you to the you are looking for. There very well may be a cleaner/shorter solution, but this was a quick work through.

//Load Source data. RowNo must be assigned according to presorted ArtId, Dossier, and Data_Price_from fields
source:
load * inline [
ArtId, Dossier, Data_Price_from, RowNo
1002, 1000, 01.01.2018, 1
1002, 1000, 17.10.2018, 2
1002, 1000, 12.09.2020, 3
1002, 1000, 26.03.2021, 4
1003, 1000, 23.10.2018, 5
1003, 1000, 12.09.2020, 6
1003, 1000, 26.03.2021, 7
1004, 1000, 23.10.2018, 8
1004, 1000, 12.09.2020, 9
1004, 1000, 26.03.2021, 10
1006, 1000, 23.10.2018, 11
1020, 808152, 10.01.2022, 12
1020, 808371, 14.01.2022, 13
1020, 810664, 08.03.2022, 14
1020, 810916, 15.03.2022, 15
1016, 819846, 18.10.2022, 16
1016, 819874, 18.10.2022, 17
];

//intermediate step to reload source with Key1 to be used for a join
Intermediate:
Load *,
ArtId&Dossier as Key1
Resident source;

//finding the Max date for each artID and Dossier combination and joining it into the newly created intermediate table
left join (Intermediate)
Load
ArtId&Dossier as Key1,
max(Date(date#(Data_Price_from,'DD.MM.YYYY'),'DD.MM.YYYY')) as MaxDate
Resident [source]
group by ArtId&Dossier;
drop table source; //dropping original source to stay clean since our new data is in Intermediate table

//Building a final table where we do the final logic to create Data_price_to field. It will equal todays date when the data_price_from field equals the max date found for the artid&dossier combination. otherwise it will peek() to pull the value from the prior row which is sorted in descending order.
Final:
Load *,
If (Data_Price_from = [MaxDate], Date(today(),'DD.MM.YYYY'),Date(date#(Peek(Data_Price_from),'DD.MM.YYYY')-1,'DD.MM.YYYY')) as Data_Price_to
Resident Intermediate
Order By RowNo desc;
drop table Intermediate; //dropping intermediate table to stay clean since our final data is in Final table

View solution in original post

AustinSpivey
Partner - Creator
Partner - Creator

I think the other solutions that recommend using the Peek() function are correct. This is how I'd do it:

AustinSpivey_0-1700592466452.png

What it does is first reorder the table on line 29 so that when we use the Peek() function, it's "looking up" at the correct row. Then on lines 24-27, we check to see if the current row has the same [ArtId] and [Dossier] values as the row above it. If it does, then we take the [Data_Price_from] from the row above it and subtract 1 day. Otherwise, we return the current date using the Today() function. That whole expression is wrapped in the Date() function and is used to create a new [Data_Price_to] field.

This is the output I get:

AustinSpivey_1-1700593063038.png

I noticed that my output is a little different from yours in the cases where [ArtId] matches the row below it but [Dossier] is different so maybe you'd need to fix my logic somewhere.

Here's that expression I used:

Date(If( Peek([ArtId]) = [ArtId]  AND  Peek([Dossier]) = [Dossier]
      , Peek([Data_Price_from]) - 1
      , Today()
    )) as [Data_Price_to]
Austin Spivey | Principal Consultant @ Arc Analytics
ArcAnalytics.us | Add me on LinkedIn

View solution in original post

3 Replies
Jebrezov
Contributor III
Contributor III

There is a peek() function in Qlik which allows the user to view a prior record already loaded. this means on the load for this example, we need to sort things in descending order based on the RowNo. It is a big assumption that the Row number is assigned from the source after being sorted by ArtId, Dossier, and Data_Price_from fields in ascending order. If that is not the case, this solution will likely need to be modified.

Below is what i think gets you to the you are looking for. There very well may be a cleaner/shorter solution, but this was a quick work through.

//Load Source data. RowNo must be assigned according to presorted ArtId, Dossier, and Data_Price_from fields
source:
load * inline [
ArtId, Dossier, Data_Price_from, RowNo
1002, 1000, 01.01.2018, 1
1002, 1000, 17.10.2018, 2
1002, 1000, 12.09.2020, 3
1002, 1000, 26.03.2021, 4
1003, 1000, 23.10.2018, 5
1003, 1000, 12.09.2020, 6
1003, 1000, 26.03.2021, 7
1004, 1000, 23.10.2018, 8
1004, 1000, 12.09.2020, 9
1004, 1000, 26.03.2021, 10
1006, 1000, 23.10.2018, 11
1020, 808152, 10.01.2022, 12
1020, 808371, 14.01.2022, 13
1020, 810664, 08.03.2022, 14
1020, 810916, 15.03.2022, 15
1016, 819846, 18.10.2022, 16
1016, 819874, 18.10.2022, 17
];

//intermediate step to reload source with Key1 to be used for a join
Intermediate:
Load *,
ArtId&Dossier as Key1
Resident source;

//finding the Max date for each artID and Dossier combination and joining it into the newly created intermediate table
left join (Intermediate)
Load
ArtId&Dossier as Key1,
max(Date(date#(Data_Price_from,'DD.MM.YYYY'),'DD.MM.YYYY')) as MaxDate
Resident [source]
group by ArtId&Dossier;
drop table source; //dropping original source to stay clean since our new data is in Intermediate table

//Building a final table where we do the final logic to create Data_price_to field. It will equal todays date when the data_price_from field equals the max date found for the artid&dossier combination. otherwise it will peek() to pull the value from the prior row which is sorted in descending order.
Final:
Load *,
If (Data_Price_from = [MaxDate], Date(today(),'DD.MM.YYYY'),Date(date#(Peek(Data_Price_from),'DD.MM.YYYY')-1,'DD.MM.YYYY')) as Data_Price_to
Resident Intermediate
Order By RowNo desc;
drop table Intermediate; //dropping intermediate table to stay clean since our final data is in Final table

AustinSpivey
Partner - Creator
Partner - Creator

I think the other solutions that recommend using the Peek() function are correct. This is how I'd do it:

AustinSpivey_0-1700592466452.png

What it does is first reorder the table on line 29 so that when we use the Peek() function, it's "looking up" at the correct row. Then on lines 24-27, we check to see if the current row has the same [ArtId] and [Dossier] values as the row above it. If it does, then we take the [Data_Price_from] from the row above it and subtract 1 day. Otherwise, we return the current date using the Today() function. That whole expression is wrapped in the Date() function and is used to create a new [Data_Price_to] field.

This is the output I get:

AustinSpivey_1-1700593063038.png

I noticed that my output is a little different from yours in the cases where [ArtId] matches the row below it but [Dossier] is different so maybe you'd need to fix my logic somewhere.

Here's that expression I used:

Date(If( Peek([ArtId]) = [ArtId]  AND  Peek([Dossier]) = [Dossier]
      , Peek([Data_Price_from]) - 1
      , Today()
    )) as [Data_Price_to]
Austin Spivey | Principal Consultant @ Arc Analytics
ArcAnalytics.us | Add me on LinkedIn