Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
A7R3
Contributor III
Contributor III

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

Hi,

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

YearMonthProductStatus01
202011ANo
202011BNo
202011CNo
202012AYes
202012BNo
202101ANo
202101BNo
202101CYes

 

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.

ProductStatus02
AYes
BNo
CYes

 

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

YearMonthProductStatus01Status02
202011ANoNo
202011BNoNo
202011CNoNo
202012AYesYes
202012BNoNo
202101ANoYes
202101BNoNo
202101CYesYes

 

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.

Labels (2)
2 Solutions

Accepted Solutions
saran7de
Master
Master

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;

View solution in original post

A7R3
Contributor III
Contributor III
Author

Hi @saran7de ,

The  FirstSortedValue functions should work here. Thanks. 

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

Great to learn from you.

View solution in original post

15 Replies
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;

Thanks & Regards,
Mayil Vahanan R
A7R3
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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;

Thanks & Regards,
Mayil Vahanan R
A7R3
Contributor III
Contributor III
Author

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.

saran7de
Master
Master

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;

 

saran7de
Master
Master

commQV74.PNG

MayilVahanan

HI @A7R3 

Can you send the sample data and expected output.

Thanks & Regards,
Mayil Vahanan R
A7R3
Contributor III
Contributor III
Author

Hi  @saran7de ,

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.