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 | - | - |
Accepted Solutions
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
Hola Brothermuffin
Logre hacer lo que necesitas con el siguiente script
t:
Load *
Where x <> 0;
LOAD [Product ID],
[Product Name],
IDO,
[Feature ID],
[KRI Report Date],
if( [Product ID] = 'RK1' AND IsNull([Feature ID])=true(),Coalesce([Feature ID],0),[Feature ID]) as x
FROM
[..\..\..\coalesce.xlsx]
(ooxml, embedded labels, table is EmptyIsNull);
drop field x;
lo que hize fue crear otro campo que reemplazara al actual '[Feature ID]' lo denomine 'x'
Le puse una condición que me ayuda a buscar el '[Product ID]' = 'RK1'
Busque el valor nulo con isnull([Feature ID])=true()) que quiere decir en lenguaje natural , si es verdad que [Feature ID] tiene el simbolo '-' o es nulo,
Con la función condicional Coalasce lo que hizo fue reemplazar el valor de '-' por cero, esto con la idea de poner un where que cargue todos los valores donde x(campo nuevo) sea diferente de 0
Despues hize una carga precedente(Primer Load), y le dije carga todos los campos de la tabla de abajo donde 'x' sea diferente de 0.
Puedes eliminar el campo 'x' y [Feature ID] ya no mostrara el valor '-' para [Feature ID]
Resultado
lo bueno de hacerlo de esta forma es que no tendras que preocuparte de estar agregando expresiones a los graficos, para quitar '-' para '[Product ID]' = 'RK1'
Regalame un like para saber si logre ayudarte, si tienes dudas, no dudes en preguntar
Luis Vallejo.
Hi Luis,
Thank you for this potential solution.
Question. We have multiple products (hundreds).
It appears that the '-' symbol is appearing randomly on different products.
I have an example below:
Thank you again. Sorry for not being clear in the beginning.
I greatly appreciate your help.
Hi Luis,
I tried to replicate your solution. But I am not getting the same results as you
T:
Load *
Where x <> 0 ;
LOAD
[Product ID],
[Product Name],
IDO,
[Feature ID],
[Report Date],
if([Product ID] = 'RK1' AND IsNull([Feature ID])=true(), Coalesce([Feature ID],0) , [Feature ID]) as x
FROM [lib://Test/sample.csv]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq);
drop field x;
See results:
