topic Re: How to create updated field within the group without aggregation in load script? in App Development

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

A7R3 — Sat, 16 Nov 2024 00:51:01 GMT

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.

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

MayilVahanan — Mon, 11 Jan 2021 01:30:31 GMT

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-group-without-aggregation/td-p/1773188]
(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;

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

A7R3 — Mon, 11 Jan 2021 02:04:48 GMT

Thanks for the super fast response.

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

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

Examples to follow

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

A7R3 — Mon, 11 Jan 2021 02:08:56 GMT

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

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

MayilVahanan — Mon, 11 Jan 2021 02:46:30 GMT

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;

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

A7R3 — Mon, 11 Jan 2021 03:02:59 GMT

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.

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

Saravanan_Desingh — Mon, 11 Jan 2021 03:24:31 GMT

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;

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

Saravanan_Desingh — Mon, 11 Jan 2021 03:25:04 GMT

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

MayilVahanan — Mon, 11 Jan 2021 04:12:34 GMT

HI @A7R3

Can you send the sample data and expected output.

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

A7R3 — Mon, 11 Jan 2021 04:16:04 GMT

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.

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

A7R3 — Mon, 11 Jan 2021 04:45:11 GMT

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

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

A7R3 — Mon, 11 Jan 2021 22:47:10 GMT

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.

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

Saravanan_Desingh — Mon, 11 Jan 2021 23:54:58 GMT

Is it what u are looking for?

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

Saravanan_Desingh — Mon, 11 Jan 2021 23:56:41 GMT

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.

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

A7R3 — Tue, 12 Jan 2021 00:19:17 GMT

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.

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

Saravanan_Desingh — Tue, 12 Jan 2021 00:27:11 GMT

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