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: 
Bhushan1
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.

 

Regards,

Bhushan

Labels (1)
1 Reply
TimvB
Creator II
Creator II

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;

 

Duplicate stores.PNG

 

 

 

Hope it helps!