Qlik Community

App Development

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

IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

How to ignore subfield duplicates in qlik sense table

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 IDTYPEStateWeight
11st timeA2
21st timeB|C2


we need to display the out like below 

TypeCount of stores
1st time4

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




1 Reply
Creator II
Creator II

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
AutoGenerate 0;

Sub AddNewStore(vStoreID, vTYPE, vState)
    Concatenate (Fact_Transform)
	Load * Inline [
    	StoreID, TYPE, State
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;


Duplicate stores.PNG




Hope it helps!