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 | - | - |
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.
Hola brothermuffin
Puedes por favor enviar una hoja de excel tal cual como lo necesitas
y otra hoja con los datos tal cual los tienes ahora
para poder ayudarte.
Hi Louis,
See sample
Product ID | Product Name | IDO | Feature ID | Report Date |
RK1 | Sample 1 | AZ0205 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0205 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0205 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0205 | - | - |
RK1 | Sample 1 | AZ0206 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0206 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0206 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0206 | - | - |
RK1 | Sample 1 | AZ0209 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0209 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0209 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0209 | - | - |
RK1 | Sample 1 | AZ0258 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0258 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0258 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0258 | - | - |
RK1 | Sample 1 | AZ0266 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | - | - |
RK1 | Sample 1 | AZ0266 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | - | - |
RK1 | Sample 1 | AZ0266 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0266 | - | - |
RK1 | Sample 1 | AZ0267 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0267 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0267 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0267 | - | - |
RK1 | Sample 1 | AZ0268 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0268 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0268 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0268 | - | - |
RK1 | Sample 1 | AZ0279 | KR00704 | 30-Apr-22 |
RK1 | Sample 1 | AZ0279 | KR00705 | 30-Apr-22 |
RK1 | Sample 1 | AZ0279 | KR01372 | 30-Apr-22 |
RK1 | Sample 1 | AZ0279 | - | - |
RK2 | Sample 12312312 | AZ0263 | - | - |
RK3 | Sample 151531515 | AZ0088 | - | - |
RK3 | Sample 151531515 | AZ0210 | - | - |
RK3 | Sample 151531515 | AZ0211 | - | - |
RK3 | Sample 151531515 | AZ0211 | - | - |
RK3 | Sample 151531515 | AZ0230 | - | - |
RK3 | Sample 151531515 | AZ0231 | - | - |
RK3 | Sample 151531515 | AZ0261 | - | - |
RK3 | Sample 151531515 | AZ0261 | - | - |
RK3 | Sample 151531515 | AZ0264 | - | - |
RK3 | Sample 151531515 | AZ0266 | - | - |
RK3 | Sample 151531515 | AZ0266 | - | - |
RK3 | Sample 151531515 | AZ0266 | - | - |
RK4 | Sample 2222241414 | AZ0000 | - | - |
RK4 | Sample 2222241414 | AZ0000 | - | - |
RK4 | Sample 2222241414 | AZ0221 | - | - |
RK4 | Sample 2222241414 | AZ0221 | - | - |
RK4 | Sample 2222241414 | AZ0260 | - | - |
RK4 | Sample 2222241414 | AZ0261 | - | - |
RK4 | Sample 2222241414 | AZ0261 | - | - |
RK4 | Sample 2222241414 | AZ0271 | - | - |
RK4 | Sample 2222241414 | AZ0271 | - | - |
RK5 | Sample 699692 | AZ0206 | - | - |
RK5 | Sample 699692 | AZ0210 | - | - |
RK5 | Sample 699692 | AZ0279 | - | - |
RK6 | Sample 6888283432 | AZ0209 | - | - |
RK6 | Sample 6888283432 | AZ0211 | - | - |
RK6 | Sample 6888283432 | AZ0211 | - | - |
RK6 | Sample 6888283432 | AZ0231 | - | - |
RK6 | Sample 6888283432 | AZ0264 | - | - |
RK6 | Sample 6888283432 | AZ0266 | - | - |
RK6 | Sample 6888283432 | AZ0266 | - | - |
RK6 | Sample 6888283432 | AZ0279 | - | - |
RK8 | Sample 151616161 | AZ0205 | KR02113 | 30-Apr-22 |
RK9 | Sample 666111323 | AZ0266 | KR02113 | 30-Apr-22 |
RK9 | Sample 666111323 | AZ0266 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0206 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0206 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0206 | - | - |
RK10 | Sample 1099094944 | AZ0210 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0210 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0210 | - | - |
RK10 | Sample 1099094944 | AZ0210 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0210 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0210 | - | - |
RK10 | Sample 1099094944 | AZ0230 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0230 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0230 | - | - |
RK10 | Sample 1099094944 | AZ0230 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0230 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0230 | - | - |
RK10 | Sample 1099094944 | AZ0261 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0261 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0261 | - | - |
RK10 | Sample 1099094944 | AZ0263 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0263 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0263 | - | - |
RK10 | Sample 1099094944 | AZ0266 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0266 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0266 | - | - |
RK10 | Sample 1099094944 | AZ0266 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0266 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0266 | - | - |
RK10 | Sample 1099094944 | AZ0267 | KR02113 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0267 | KR02114 | 30-Apr-22 |
RK10 | Sample 1099094944 | AZ0267 | - | - |
RK11 | Sample 00009345345 | AZ0230 | - | - |
RK11 | Sample 00009345345 | AZ0230 | - | - |
RK11 | Sample 00009345345 | AZ0230 | - | - |
RK11 | Sample 00009345345 | AZ0257 | - | - |
RK11 | Sample 00009345345 | AZ0257 | - | - |
RK11 | Sample 00009345345 | AZ0257 | - | - |
RK11 | Sample 00009345345 | AZ0258 | - | - |
RK11 | Sample 00009345345 | AZ0291 | - | - |
RK12 | Sample 6699191329414141 | AZ0230 | - | - |
RK12 | Sample 6699191329414141 | AZ0267 | - | - |
Thank you, Louis,
see sample data - https://jsfiddle.net/9ys2o81u/
Hola brothermuffin
Ensaya por favor este script
T:
Load *
Where x <> 0 ;
LOAD
[Product ID],
[Product Name],
IDO,
[Feature ID],
[Report Date],
if(WildMatch([Product ID],'RK*') AND IsNull([FeatureID])=true()[Feature ID]) as x
FROM
[..\..\..\coalesce.xlsx]
(ooxml, embedded labels, table is comu);
drop field x;
-------------------------------------------------------------------------
Nota: puedes tambien elegir el product ID que prefieras eliminar del siguiente modo
WildMatch([Product ID],'RK1','Rk2' etc)
Regalame un like para saber si logre ayudarte, si tienes dudas, no dudes en preguntar 😀
Luis Vallejo.
Thank you Luis, I will look at this and get back to you if I have any questions
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:
Hola brothermuffin
La vez que me enviaste Los datos
Los pase a UN excel y Los cargue en Qlik
Me sucedio lo mismo, El problema era que estaba digitado El caracter '-' y la función Coalasce retorna 0 solo si es UN valor null()
Los valores null son El caracter '-' pero qlik Los asigna automaticamente cuando no hay registros en Los Campos , la solucion fue entrar Al archivo y borrar todos Los caracteres '-' que estaban en Los registros.
Regalame un like para saber si logre ayudarte, si tienes dudas, no dudes en preguntar 😀
Luis Vallejo.