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.

[MayilVahanan]

HI @A7R3 

You can remove the Group by statement in ur load statement. 

Try like below

Temp:
LOAD YearMonth,
Product,
Status01
FROM
[https://community.qlik.com/t5/Qlik-Sense-Advanced-Authoring/How-to-create-updated-field-within-the-g...]
(html, codepage is 1252, embedded labels, table is @1);

Load IF(Previous(Status01)='Yes', Previous(Status01), Status01) AS Status02, *
Resident Temp
Order by Product ASC, YearMonth ASC;

DROP Table Temp;

[A7R3]

Thanks for the super fast response.

That is where I was at initially. There are 2 problems with this.

1. I get the Status02 carried forward from another Product. Once it is evaluating the Status01 for the next product, it should reset the Status02.
2. On the 4th row, if the Status01 is No, the Status02 will become No. It should stay as Yes.

Examples to follow

[A7R3]

I get the Status02 carried forward from another Product. Once it is evaluating the Status01 for the next product, it should reset the Status02.

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

For the same products, the status should stay as Yes.
On the 4th row, if the Status01 is No, the Status02 will become No. It should stay as Yes.

YearMonth Product Status01 Status02
202011 A No No
202012 A Yes Yes
202101 A No Yes
202102 A No No

[MayilVahanan]

HI @A7R3 

May be try like below

Load IF(Previous(Status01)='Yes' and Product = Previous(Product), Peek('Status02'), Status01) AS Status02, *
Resident Temp
Order by Product ASC, YearMonth ASC;

[A7R3]

Hi Mayil,

Thanks for the suggestion. 

It solves the  carry forward problem.

Still need help to fix the issue to keep the Statu02 the same within the same Product.

[Saravanan_Desingh]

One  solution is.

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
Resident tab1
Group By Product;

[Saravanan_Desingh]

[MayilVahanan]

HI @A7R3 

Can you send the sample data and expected output.

[A7R3]

Hi  @Saravanan_Desingh ,

Your suggestion goes right to the final solution. It is working. Thanks.

However, I will still need some help to tweak the script

1. The value of the Status01 is actually more than Yes and No. I have 5 different values  in this column. How can the scrip be changed to output the actual value, instead of 'Yes' and 'No'?

2.  With this solution, for one product, the entire column of Status02 are 'Yes' once the condition is met. Is there anyway to update the Status02 only if Status='Yes1" or "Yes02?

YearMonth Product Status01 Status02
202011        A               No No         <----------Keep Status02 = Status01
202012        A               Yes Yes
202101        A               No Yes
202102       A                No Yes

Thanks in advance.