Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 | - | - |
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
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
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