How to ignore subfield duplicates in qlik sense table
I have requirement like below.
I have two store's (Store 1 and Store 2) and also I have a column weight , so if the first store weight shows 2 then we need to duplicate entire row as two times and we have also weight is 2 for store 2 as well. so here also we need to duplicate two times, after duplicating we will get total 4 rows for 2 stores.
we have achieved until this one with the help of while loop. but we have a another column called State which contains data like 'B | C' , this column we need to show as separate rows, to get this one we are using "sub field" function. so for the store 2 we are getting 4 rows (2 rows for B and 2 rows for C).
we need to display the out like below
Count of stores
How to get store count as 4 instead of 6. Please refer the attached qvf also for the same.
The following script will give the result as in the table:
load * inline [
1, '1st time',A,2
2, '1st time',B|C,2
0 as StoreID,
0 as TYPE,
0 as State
Sub AddNewStore(vStoreID, vTYPE, vState)
Load * Inline [
StoreID, TYPE, State
//=== Loop over the stores
For i = 0 to (NoOfRows('Fact_Extract') - 1)
//=== Create duplicate rows if 'weight' is larger than 1
For j = 1 to Peek('Weight','$(i)','Fact_Extract')
Let vStoreID = Peek('StoreID','$(i)','Fact_Extract');
Let vTYPE = Peek('TYPE','$(i)','Fact_Extract');
Let vState = subfield(Peek('State','$(i)','Fact_Extract'),'|','$(j)');
Call AddNewStore('$(vStoreID)', '$(vTYPE)', '$(vState)');
Store Fact_Transform into [lib://Data/Fact_Transform.qvd];
Drop Table Fact_Extract;