Peek and Wildmatch

[crichter14]

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.

[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 · Accepted solution]

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;