Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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 for the super fast response.
That is where I was at initially. There are 2 problems with this.
Examples to follow
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
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;
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.
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;
HI @A7R3
Can you send the sample data and expected output.
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.