How to ignore subfield duplicates in qlik sense table

[Bhushan1]

Hi All,

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).

Store ID	TYPE	State	Weight
1	1st time	A	2
2	1st time	B|C	2

we need to display the out like below 

Type	Count of stores
1st time	4

How to get store count as 4 instead of 6. Please refer the attached qvf also for the same.

Regards,

Bhushan

[TimvB]

The following script will give the result as in the table:

Fact_Extract:
load * inline [
StoreID,TYPE,State,Weight
1, '1st time',A,2
2, '1st time',B|C,2
];

Fact_Transform:
LOAD 
	0 as StoreID, 
	0 as TYPE,
    0 as State
AutoGenerate 0;

Sub AddNewStore(vStoreID, vTYPE, vState)
    Concatenate (Fact_Transform)
	Load * Inline [
    	StoreID, TYPE, State
    	$(vStoreID),$(vTYPE),$(vState)
    ];    
End Sub

//=== 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)'); 
        
    Next j

Next i;

Store Fact_Transform into [lib://Data/Fact_Transform.qvd];

Drop Table Fact_Extract;

Hope it helps!