Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Alm
Partner - Creator
Partner - Creator

Transform with Peek and loop?

Hi,

I have a set of data that I need to transform.

The data looks like this:

ID ProductID Descr unit Rowno
1 2 2 products sold $100 Piece 1
1 3 1 product sold $50 Piece 2
1   Period 2022-01-01 - 2022-02-01   3
1       4
1 2 1 product sold $50 Piece 5
1 3 4 products sold $250  Piece 6
1   Period 2022-03-01 - 2022-04-01    7

 

 

What I need to solve is the following.

Row 7 contains two dates: 2022-03-01 and 2022-04-01. I need to put these as columns on row 5 & 6 since these periods belong to these rows. 

Row 3 belongs to row 1 & 2.

The pattern is that rows that starts with "descr" = Period belongs to the rows above that has a ProductID. And ends on when the row above is null or there is no row above the first row that contains a ProductId

 

The final table should look somewhat like this. 

ID ProductID Descr unit Rowno Period start Period End
1 2 2 products sold $100 Piece 1 2022-01-01 2022-02-01
1 3 1 product sold $50 Piece 2 2022-01-01 2022-02-01
1   Period 2022-01-01 - 2022-02-01   3    
1       4    
1 2 1 product sold $50 Piece 5 2022-03-01 2022-04-01
1 3 4 products sold $250  Piece 6 2022-03-01 2022-04-01
1   Period 2022-03-01 - 2022-04-01    7    

 

Does anyone have experience of doing something similar?

Kind regards, Jonathan

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

Hi

Try like below

Temp:
Load * Inline
[
ID ProductID Descr unit Rowno
1 2 2 products sold $100 Piece 1
1 3 1 product sold $50 Piece 2
1 Period 2022-01-01 - 2022-02-01 3
1 4
1 2 1 product sold $50 Piece 5
1 3 4 products sold $250 Piece 6
1 Period 2022-03-01 - 2022-04-01 7
](delimiter is ' ');

Join
Load ID, Rowno, Mid(Descr,7,11) as PeriodStartDate, Right(Descr,10) as PeriodEndDate Resident Temp
where WildMatch(Descr, '*Period*');

Final:
NoConcatenate
Load ID, ProductID,Descr, unit, Rowno, if(Isnull(PeriodStartDate), Peek(PeriodStartDate), PeriodStartDate) as PeriodStartDate
, if(Isnull(PeriodEndDate), Peek(PeriodEndDate), PeriodEndDate) as PeriodEndDate
Resident Temp Order by Rowno desc;

Drop Table Temp;

O/p:

MayilVahanan_0-1655282162975.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

2 Replies
MayilVahanan

Hi

Try like below

Temp:
Load * Inline
[
ID ProductID Descr unit Rowno
1 2 2 products sold $100 Piece 1
1 3 1 product sold $50 Piece 2
1 Period 2022-01-01 - 2022-02-01 3
1 4
1 2 1 product sold $50 Piece 5
1 3 4 products sold $250 Piece 6
1 Period 2022-03-01 - 2022-04-01 7
](delimiter is ' ');

Join
Load ID, Rowno, Mid(Descr,7,11) as PeriodStartDate, Right(Descr,10) as PeriodEndDate Resident Temp
where WildMatch(Descr, '*Period*');

Final:
NoConcatenate
Load ID, ProductID,Descr, unit, Rowno, if(Isnull(PeriodStartDate), Peek(PeriodStartDate), PeriodStartDate) as PeriodStartDate
, if(Isnull(PeriodEndDate), Peek(PeriodEndDate), PeriodEndDate) as PeriodEndDate
Resident Temp Order by Rowno desc;

Drop Table Temp;

O/p:

MayilVahanan_0-1655282162975.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Thanks, that did the trick!