Search Up

[John-SSI]

Hi Forum,

I got 2 fields in sam table, 

Data 1 (is an Article)
Data 2 (Is a freetext)

And then a got a table in Qlik

If Data 1 contains text "N2332" and Data 2  contains text "N2332" then i will present all the post contains this.

Example;

Date	Art	Place	Stock	Freetext
2024-11-07	N233245	ABD3	3	N2332
2024-11-06	N000034	DEC4	3	N2332
2024-11-06	N2332	DEC67	7	IR24553

So when i search in this new (Data1 (Art) and Data2 (Freetext)) i want all the hits when a search on this word N2332 above.

Doable?

[Qrishna]

if i understand you correctly, you are looking  for way to search the value 'N2332 ' in multiple fields so called Art and Freetext, this can be don using wild search/ fuzzy search. if you do regular search it shows up only from one field with the exact match.

[John-SSI]

Okay, how do i combine these 2 rows in script?

Alt(Art , Freetext) as Test

Or do i missing the search object in Qlik Qloud. I remeber in QlikView there was a searchobject there you can point ot the data to search.

? 🙂

[Qrishna]

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. 

i believe you are looking for mapsubstring logic appied to two different fields. something like below:

MapText:
// Mapping table
Mapping
Load Code,
'{' & Flag & '}' as MapText
Inline [
Code, Flag
N2332, 1
];

Data:
Load *,
if(Column1 = 1 and Column2 = 1, 'Art, FreeText',
if(Column1 = 1, 'Art', if(Column2 = 1, 'FreeText', 0))) as Field_Flag;
Load *,
MapSubString('MapText', Art) as MappedText1,
TextBetween(MapSubString('MapText', Art), '{', '}') as Column1,
MapSubString('MapText', Freetext) as MappedText2,
TextBetween(MapSubString('MapText', Freetext), '{', '}') as Column2
Inline [
Date, Art, Place, Freetext
2024-11-07, N233245, ABD3, N2332
2024-11-06, N000034, DEC4, N2332
2024-11-06, N2332, DEC67, IR24553
];

//drop fields Column1, Column2, MappedText1, MappedText2;

exit script;

[John-SSI]

Yeah, exact!

[Qrishna]

Updated my above post with code.

[John-SSI]

Nice, i will test.

This first code:
Mapping
Load Code,
'{' & Category & '}' as MapText
I have a Excel where the data is so no inline. Category i don't have

[Qrishna]

provide the excel will update the code.

[Qrishna]

MapText:
// Mapping table
Mapping
Load Code,
'{' & Flag & '}' as MapText
Inline [
Code, Flag
N2332, 1
];

Data:
Load *,
if(Column1 = 1 and Column2 = 1, 'Art, FreeText',
if(Column1 = 1, 'Art', if(Column2 = 1, 'FreeText', 0))) as Field_Flag;
Load *,
MapSubString('MapText', Art) as MappedText1,
TextBetween(MapSubString('MapText', Art), '{', '}') as Column1,
MapSubString('MapText', Freetext) as MappedText2,
TextBetween(MapSubString('MapText', Freetext), '{', '}') as Column2
;
LOAD Date,
Art,
Place,
Stock,
Freetext
FROM
[SampleData.xlsx]
(ooxml, embedded labels, table is Blad1);

exit script;