Hide values in Table Dimension

[brothermuffin]

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. 

Table

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

[brothermuffin]

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 

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]

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 · Accepted solution]

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 

Thank you again Louis, for helping initially.

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

Cheers