Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Re: Create Filter using words of certain Field

I changed the strings of some devices for you to see what works. ok?

Highlighted

Re: Create Filter using words of certain Field

Hi,

maybe helpful:

QlikCommunity_Thread_236807_Pic1.JPG

QlikCommunity_Thread_236807_Pic2.JPG

QlikCommunity_Thread_236807_Pic3.JPG

QlikCommunity_Thread_236807_Pic4.JPG

QlikCommunity_Thread_236807_Pic5.JPG

QlikCommunity_Thread_236807_Pic6.JPG

mapColour:

Mapping

LOAD *, '@start@'&Colour&'@end@' INLINE [

    Colour

    Branco

    Dourado

    Prateado

    Preto

];

mapCondition:

Mapping

LOAD *, '@start@'&Condition&'@end@'  INLINE [

    Condition

    Muito Bom

    Excelente

    Bom

];

mapCapacity:

Mapping

LOAD *, '@start@'&Capacity&'@end@' INLINE [

    Capacity

    16GB

    32GB

    128GB

];

mapConnectivity:

Mapping

LOAD *, '@start@'&Connectivity&'@end@'  INLINE [

    Connectivity

    Wi-Fi

    3G

    4G

];

tabModels:

LOAD RecNo() as ModelID,

     Model,

     TextBetween(MapSubString('mapColour',Model),'@start@','@end@') as Colour,

     TextBetween(MapSubString('mapCondition',Model),'@start@','@end@') as Condition,

     TextBetween(MapSubString('mapCapacity',Model),'@start@','@end@') as Capacity,

     Ceil(Rand()*100) as SomeRandomFact;

LOAD Replace(Model,' + ',' ') as Model

Inline [

Model

Blackberry 9900 Seminovo Bom

Blackberry 9900 Seminovo Excelente

Blackberry 9900 Seminovo Muito Bom

Blackberry Q10 Preto Seminovo Bom

Blackberry Q10 Preto Seminovo Excelente

Blackberry Q10 Preto Seminovo Muito Bom

Blackberry Z10 Preto Seminovo Bom

Blackberry Z10 Preto Seminovo Excelente

Blackberry Z10 Preto Seminovo Muito Bom

Blackberry Z30 Preto Seminovo Bom

Blackberry Z30 Preto Seminovo Excelente

Blackberry Z30 Preto Seminovo Muito Bom

iPad 2 Wi-Fi + 3G 16GB Branco Seminovo Bom

iPad 2 Wi-Fi + 3G 16GB Branco Seminovo Excelente

iPad 2 Wi-Fi + 3G 16GB Branco Seminovo Muito Bom

iPad 2 Wi-Fi + 3G 16GB Preto Seminovo Bom

iPad 2 Wi-Fi + 3G 16GB Preto Seminovo Excelente

iPad 2 Wi-Fi + 3G 16GB Preto Seminovo Muito Bom

iPad 2 Wi-Fi + 3G 32GB Branco Seminovo Bom

iPad 2 Wi-Fi + 3G 32GB Branco Seminovo Excelente

iPad 2 Wi-Fi + 3G 32GB Branco Seminovo Muito Bom

iPad 2 Wi-Fi + 3G 32GB Preto Seminovo Bom

iPad 2 Wi-Fi + 3G 32GB Preto Seminovo Excelente

iPad 2 Wi-Fi + 3G 32GB Preto Seminovo Muito Bom

iPad 2 Wi-Fi 16GB Branco Seminovo Bom

iPad 2 Wi-Fi 16GB Branco Seminovo Excelente

iPad 2 Wi-Fi 16GB Branco Seminovo Muito Bom

iPad 2 Wi-Fi 16GB Preto Seminovo Bom

iPad 2 Wi-Fi 16GB Preto Seminovo Excelente

iPad 2 Wi-Fi 16GB Preto Seminovo Muito Bom

iPad 2 Wi-Fi 32GB Branco Seminovo Bom

iPad 2 Wi-Fi 32GB Branco Seminovo Excelente

iPad 2 Wi-Fi 32GB Branco Seminovo Muito Bom

iPad 2 Wi-Fi 32GB Preto Seminovo Bom

iPad 2 Wi-Fi 32GB Preto Seminovo Excelente

iPad 2 Wi-Fi 32GB Preto Seminovo Muito Bom

iPad Air 2 Wi-Fi 32GB Dourado Seminovo Bom

iPad Air 2 Wi-Fi 32GB Dourado Seminovo Excelente

iPad Air 2 Wi-Fi 32GB Dourado Seminovo Muito Bom

iPad Air 2 Wi-Fi 32GB Prateado Seminovo Bom

iPad Air 2 Wi-Fi 32GB Prateado Seminovo Excelente

iPad Air 2 Wi-Fi 32GB Prateado Seminovo Muito Bom

iPad Air 2 Wi-Fi 128GB Dourado Seminovo Bom

iPad Air 2 Wi-Fi 128GB Dourado Seminovo Excelente

iPad Air 2 Wi-Fi 128GB Dourado Seminovo Muito Bom

iPad Air 2 Wi-Fi 128GB Prateado Seminovo Bom

iPad Air 2 Wi-Fi 128GB Prateado Seminovo Excelente

iPad Air 2 Wi-Fi 128GB Prateado Seminovo Muito Bom

iPad Air 2 Wi-Fi + 4G 32GB Dourado Seminovo Bom

iPad Air 2 Wi-Fi + 4G 32GB Dourado Seminovo Excelente

iPad Air 2 Wi-Fi + 4G 32GB Dourado Seminovo Muito Bom

iPad Air 2 Wi-Fi + 4G 32GB Prateado Seminovo Bom

iPad Air 2 Wi-Fi + 4G 32GB Prateado Seminovo Excelente

iPad Air 2 Wi-Fi + 4G 32GB Prateado Seminovo Muito Bom

iPad Air 2 Wi-Fi + 4G 128GB Dourado Seminovo Bom

iPad Air 2 Wi-Fi + 4G 128GB Dourado Seminovo Excelente

iPad Air 2 Wi-Fi + 4G 128GB Dourado Seminovo Muito Bom

iPad Air 2 Wi-Fi + 4G 128GB Prateado Seminovo Bom

iPad Air 2 Wi-Fi + 4G 128GB Prateado Seminovo Excelente

iPad Air 2 Wi-Fi + 4G 128GB Prateado Seminovo Muito Bom

];

tabConnectivity:

LOAD ModelID,

     TextBetween(MapSubString('mapConnectivity',Model),'@start@','@end@',IterNo()) as Connectivity

Resident tabModels

While IterNo()<=SubStringCount(MapSubString('mapConnectivity',Model),'@start@');

tabModelGroups:

LOAD ModelID,

     IterNo() as Level,

     Trim(Left(Model,Index(Model&' ',' ',IterNo()))) as ModelGroup

Resident tabModels

While IterNo()<=SubStringCount(Model,' ')+1;

tabModelGrpLvl:

Generic

LOAD ModelID,

     'ModelGrpLvl'&Level,

     ModelGroup

Resident tabModelGroups;

regards

Marco

Highlighted
Not applicable

Re: Create Filter using words of certain Field

Marco,

Thank you very much, great QV!!!

The only thing I don't how to do is: instead of using inline (and listing manually all the models), how can I do the same but extracting the models directly from an excel?

For example the excel root is: C:\Users\Martin\Google Drive\MKT\*.csv

Highlighted

Re: Create Filter using words of certain Field

You can replace the Inline[] part with "FROM yourcsvfile".

Best you use the File Import wizard to get the correct syntax.

Can you post some lines (incl. header) of your csv file to assist?

Regards

Marco

Highlighted
Not applicable

Re: Create Filter using words of certain Field

I tried to download the data direct from the file but was not possible, I'm making unknown mistakes.

What I must correct?