Create Filter using words of certain Field

[#1]

Hi everyone,

I need to find a way to create a Filter that contains certain words of a determinate Field.

For example: I have this list box of the field "Model"

I want to create some filters that filter the data depending on the words:

• One that contains "Bom" or Excelente"
• One that contains the brands as "Blackberry", "Samsung",etc.
• One that contains the models "iPhone 5, "Z30", etc.

I would be very grateful if someone help me.

Thanks in advance.

Martín

[Anonymous]

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

[MarcoWedel]

Hi,

maybe helpful:

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

[#4]

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

[MarcoWedel]

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

[#6]

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

What I must correct?