How to create updated field within the group without aggregation in load script?

[A7R3]

Hi,

I have a table in the QS that will load as follow.

YearMonth	Product	Status01
202011	A	No
202011	B	No
202011	C	No
202012	A	Yes
202012	B	No
202101	A	No
202101	B	No
202101	C	Yes

I would like create a second table that reports the Status by Product, where once the Status01 becomes Yes, it will be reported as Yes.

Product	Status02
A	Yes
B	No
C	Yes

I figured that I will need to add a column Status02 to get it done and reload with Product and Status02, as follow.

YearMonth	Product	Status01	Status02
202011	A	No	No
202011	B	No	No
202011	C	No	No
202012	A	Yes	Yes
202012	B	No	No
202101	A	No	Yes
202101	B	No	No
202101	C	Yes	Yes

I tried to use the following statement in the load script

IF(Previous(Status01)='Yes', Previous(Status01), Status01) AS Status02,

Group By Product,

Order by Product ASC, YearMonth ASC;

But it shows error because there is no aggregation here.

Looking for suggestion on how to make it works. Thank you in advance.

[A7R3]

Hi @MayilVahanan ,

The first 3 columns are the input, the 4th columns is the output.

Products ReportDate Status01 Status02
A 05/06/2020 Yellow Yellow
A 06/12/2020 Yellow Yellow
A 07/14/2020 Green01 Green01
B 05/08/2020 Yellow Yellow
B 06/15/2020 Yellow Yellow
B 06/16/2020 Yellow Yellow
B 06/22/2020 Yellow Yellow
B 06/23/2020 Green02 Green02
B 06/24/2020 Yellow Green02
B 06/25/2020 Yellow Green02
B 06/26/2020 Green02 Green02
B 06/29/2020 Yellow Green02
B 07/01/2020 Red Red
B 07/13/2020 Red Red
B 07/15/2020 Yellow Yellow
B 07/29/2020 Green02 Green02
C 05/08/2020 Yellow Yellow
C 05/15/2020 Yellow Yellow
C 05/28/2020 Yellow Yellow
C 06/16/2020 Yellow Yellow
C 06/18/2020 Green02 Green02
C 06/26/2020 Yellow Green02
C 06/29/2020 Yellow Green02
C 07/16/2020 Green02 Green02
C 07/29/2020 Green02 Green02

[A7R3]

Hi @Saravanan_Desingh ,

I can use your suggestion by changing the use of the Status02 slightly. Thank you for that.

LOAD Product, If(Index(Concat(DISTINCT Status01),'Yes')>0, 'Yes', '') As Status02

Is there any way to have the Date_Status02 as the date the first time Status02 becomes Yes?

Thanks in advance.

[Saravanan_Desingh · Accepted solution]

Is it what u are looking for?

tab1:
LOAD * INLINE [
    YearMonth, Product, Status01
    202011, A, No
    202011, B, No
    202011, C, No
    202012, A, Yes
    202012, B, No
    202101, A, No
    202101, B, No
    202101, C, Yes
];

Left Join(tab1)
LOAD Product, If(Index(Concat(DISTINCT Status01),'Yes')>0,'Yes','No') As Status02,
	FirstSortedValue(If(Status01='Yes',YearMonth), If(Status01='Yes',YearMonth)) As Date_Status03
Resident tab1
Group By Product;

[Saravanan_Desingh]

What if there is no 'Yes'? Also I could not understand your complete requirement.

If you could explain with a sample data and expected output, will help in quicker reaponse.

[A7R3 · Accepted solution]

Hi @Saravanan_Desingh ,

The  FirstSortedValue functions should work here. Thanks. 

FirstSortedValue(If(Status01='Yes',YearMonth), If(Status01='Yes',YearMonth)) As Date_Status03

Great to learn from you.

[Saravanan_Desingh]

If answered, please close the thread by choosing the answer. Thanks.