Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
@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;
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;