Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
crichter14
Creator
Creator

Peek and Wildmatch

I have a list in an excel file (which will keep getting bigger by user entry)

SOURCE:

QCYE2020

QCSEP2020

QCOCT2020

QCNOV2020

QCDEC2020

QCJAN2021

QCYE2021

I have a table with a comment in it (text field) that I need to add a filter to, if the comment has these characters in it (anywhere in the comment).

So I wrote this code:

List:
LOAD
Source FROM [lib://8_QS01 3_Data/3_Other_Data_Sources/4_Variables/QC_FIlters.xlsx]
(ooxml, embedded labels, table is Sheet1);

LET vQCFilter=Peek('Source',0);

ADJUSTMENTS_TEMP2:
NoConcatenate
LOAD *,
If(WildMatch([ADJ COMMENTS], '$(vQCFilter)')>0,SUBFIELD([ADJ COMMENTS],' ',1),null()) AS ADJ_PROJECTS

RESIDENT ADJUSTMENTS_TEMP1;

Problem, I only get the first or the last field using Peek.  Is there a way to create variables using a loop and then to put them in a wildmatch statement so I don't have to hard code this?

Thanks!

C.

Labels (6)
1 Solution

Accepted Solutions
crichter14
Creator
Creator
Author

Hi Kush,

Though that didn't work, you did gave me enough to keep researching, because what you had should have worked, but I was getting no results.   Not sure why.  Thank you for putting me on the right path though, it was a tremendous help.

C

Here's what I went with.

MapPrefix:
Mapping
LOAD '@' & Source,
'/'& Source &'\';
LOAD * FROM [QC_Filters.xlsx]
(ooxml, embedded labels, table is Sheet1);

ADJUSTMENTS:
NoConcatenate
LOAD *,
TextBetween(MapSubString('MapPrefix' , '@' & ValueToCompare), '/', '\') as ADJ_PROJECTS;
LOAD
*,
SUBFIELD([ADJ COMMENTS],' ',1) AS ValueToCompare
RESIDENT ADJUSTMENTS_TEMP;

 

View solution in original post

2 Replies
Kushal_Chawda

@crichter14  try below

List_Map:
mapping LOAD
Source,
'<'& Source & '>' as Map_Source 
FROM [lib://8_QS01 3_Data/3_Other_Data_Sources/4_Variables/QC_FIlters.xlsx]
(ooxml, embedded labels, table is Sheet1);


ADJUSTMENTS_TEMP2:
LOAD *,
If(TextBetween(MapSubString('List_Map',[ADJ COMMENTS]),'<','>'),SUBFIELD([ADJ COMMENTS],' ',1),null()) AS ADJ_PROJECTS
RESIDENT ADJUSTMENTS_TEMP1;
crichter14
Creator
Creator
Author

Hi Kush,

Though that didn't work, you did gave me enough to keep researching, because what you had should have worked, but I was getting no results.   Not sure why.  Thank you for putting me on the right path though, it was a tremendous help.

C

Here's what I went with.

MapPrefix:
Mapping
LOAD '@' & Source,
'/'& Source &'\';
LOAD * FROM [QC_Filters.xlsx]
(ooxml, embedded labels, table is Sheet1);

ADJUSTMENTS:
NoConcatenate
LOAD *,
TextBetween(MapSubString('MapPrefix' , '@' & ValueToCompare), '/', '\') as ADJ_PROJECTS;
LOAD
*,
SUBFIELD([ADJ COMMENTS],' ',1) AS ValueToCompare
RESIDENT ADJUSTMENTS_TEMP;