Transform with Peek and loop?

[Jonathan_Alm]

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

[MayilVahanan · Accepted solution]

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:

[Jonathan_Alm]

Thanks, that did the trick!