Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
brothermuffin
Contributor III
Contributor III

Hide values in Table Dimension

Hi All, 

I'm new to Qlik and currently working on Qlik Sense and can't seem to find a way to hide/remove a string from this table. 

TableTable

 The dataset is a mess and it has multiple '-' symbols that are duplicates. 

If you look at the table above, 

I want to hide the '-' from RK1 

But keep the '-' from RK2 

The reason I am doing this is because, RK2 does not have features -  unlike RK1 (It has KR1, KR2, KR3).

My goal is to convert the RK2 '-' to 'No Feature - Please review' and flag it in my report.

I tried this expression for a Dimension

aggr(ONLY({<[Feature ID ] -= {'-'}>} [Feature ID]),[Feature ID]) 

But it did not work as intended. 

I could create a Measure and use 

=If([Feature ID] = '-','0', textcount({<[Feature ID] -= {'-'}>}distinct [Feature ID])) 

and it does achieve the outcome I am looking for. 

But I was hoping if I could somehow use the above expression on the Table Dimension instead of the Measure. 

I hope I could get some guidance here

Thank you for your help. 

 

Here is a sample of the dataset - I have individual excel files from Jul 21 to May 22

Product ID Product Name IDO Feature ID Report Date
RK1 Sample 1 CT1 KR1 30-Apr-22
RK1 Sample 1 CT1 KR2 30-Apr-22
RK1 Sample 1 CT1 KR3 30-Apr-22
RK1 Sample 1 CT1 - -
RK2 Sample 2 CT2 - -
RK2 Sample 2 CT3 - -
RK2 Sample 2 CT4 - -
RK2 Sample 2 CT5 - -
RK2 Sample 2 CT6 - -
RK2 Sample 2 CT7 - -
RK2 Sample 2 CT8 - -
RK2 Sample 2 CT9 - -
RK2 Sample 2 CT10 - -
RK2 Sample 2 CT11 - -
RK2 Sample 2 CT12 - -
RK2 Sample 2 CT13 - -
12 Replies
brothermuffin
Contributor III
Contributor III
Author

Thanks, Luis, Yes I was able to replicate the solution using excel and deleting the '-'.

However, this is still not what I am looking for. Albeit it is getting close. Thank you again for providing your solution. 

What I am after is like below 

brothermuffin_0-1659344690965.png

But at the moment I have to flag it by using 

 

If(WildMatch([Product ID],'RK1','RK13','RK10') AND WildMatch([Feature ID], '-') , 'Flag for Removal', [Feature ID])

 

and manually adding in the relevant [Product ID]

 

WildMatch([Product ID],'RK1','RK10','RK13')

 


This is not a possible solution as there are hundreds of Product ID and I need the code to find them automatically. 

I have thought about using a variable - vFind to find the relevant [Product ID].

See below 

 

T:

// Load *
// Where x <> 0 ;

Let vFind = If(WildMatch([Product ID],'RK*') AND WildMatch([Feature ID], 'ZK*') , [Product ID], 0);

LOAD
	[Product ID],
	[Product Name],
	IDO,
	[Feature ID],
	[Report Date],
    If(WildMatch([Product ID],'$(vFind)') AND WildMatch([Feature ID], '-') , 'Flag', [Feature ID]) as y
 
FROM [lib://Test/sample.xlsx]
(ooxml, embedded labels, table is sample);

 


But it won't load correctly. 

I am not sure how can I get WildMatch to look for a string from a variable. 

 

What I am trying to achieve is  

If [Product ID] have [Feature ID] = 'ZK*' AND If [Product ID] also have [Feature ID] = '-'

Then

change that [Feature ID] = '-' value to 'Flag for removal' 

Else

[Feature ID]

Thank you

brothermuffin
Contributor III
Contributor III
Author

Hi 

I am getting close to a workaround I think. 

T:
// Load *
// Where x <> 0 ;

Let vFindx = '=chr(39)&concat(distinct z,chr(39)&' & Chr(39) & ',' & Chr(39) & '&chr(39)) & chr(39)';

LOAD
	[Product ID],
	[Product Name],
	IDO,
	[Feature ID],
	[Report Date],
	If(WildMatch([Product ID],'RK*') AND WildMatch([Feature ID], 'ZK*') , [Product ID]) as z,
    If(WildMatch([Product ID], '$(vFindx)') AND WildMatch([Feature ID], '-'), 'Flag', [Feature ID]) as y
 
FROM [lib://Test/sample.xlsx]
(ooxml, embedded labels, table is sample)
;

 I can't seem to get Load to run this variable 

$(vFindx)

in a WildMatch. This is a concatenated string for the WildMatch to search. 

If(WildMatch([Product ID], '$(vFindx)') AND WildMatch([Feature ID], '-'), 'Flag', [Feature ID]) as y

 

Thank you

brothermuffin
Contributor III
Contributor III
Author

Hi Louis, 

I believe I have managed to find the solution. Not sure if this is the right way but it does appear to work for me. I still need to review the results.

 

T:
Load *
Where [Feature ID] <> 'Flag' ;

LOAD
	[Product ID],
	[Product Name],
	IDO,
	//[Feature ID],
	[Report Date],
	If(WildMatch([Product ID],'RK*') AND WildMatch([Feature ID], 'ZK*') , [Product ID]) as z,
    If(WildMatch([Product ID],$(vList)) AND WildMatch([Feature ID], '-') , 'Flag',[Feature ID]) as [Feature ID]
 
FROM [lib://Test/sample.xlsx]
(ooxml, embedded labels, table is sample)
;

Temp:

Load 

	chr(39)&concat(distinct z,chr(39)&','&chr(39)) & chr(39) as y
    
Resident T;

Let vList = peek('y',-1,'temp');

Drop TABLE Temp;

Drop Field z;

 

I just expanded on your solution. Thank you for giving me the idea. 

All I did was create a variable to locate all the [Product ID] that I want to 'Flag' for removal. I was struggling with getting WildMatch to see the values as it all had to be in a concatenated string. ie. 'RK1','RK13,'RK10' etc. 

After searching around here.

I found https://community.qlik.com/t5/QlikView-App-Dev/Passing-a-variable-in-wildmatch/td-p/19358
where @Gysbert_Wassenaar  had an example to create a temp table, load the concatenate string to a variable 

 

Temp:

Load 

	chr(39)&concat(distinct z,chr(39)&','&chr(39)) & chr(39) as y
    
Resident T;

Let vList = peek('y',-1,'temp');

Drop TABLE Temp;

 

So I used that and incorporate it into the load script and used your original idea to remove the 'Flag' rows.

Result - I managed to remove the relevant values from the table 

brothermuffin_0-1659405027114.png

Thank you again Louis, for helping initially.

It was a great learning experience for me to figure this out. 

Cheers