Error with match() statement when comparing strings

[hcorbett]

I am trying to compare the data within two files with a field called DataKey. The DataKey is the metric number + | + the date in num format (i.e. 123|44955)

I am reading in file A as A_table and create the DataKey field 

Text(MetricID & '|' & EVENT_DATE_NUM) as DataKey

I am then reading in file B as B_temp and creating the DataKey field the same way.

I then create a list of all the DataKey values from A_table:

NoConcatenate
datakey_temp:
Load Distinct DataKey
RESIDENT A_table
ORDER BY DataKey;

Datakey_list:
load concat(distinct DataKey, ',') as Datakey_list Resident datakey_temp;

LET dataKeyList = peek('Datakey_list');

drop tables datakey_temp, Datakey_list;

I then concatenate the data from B_temp table into A_table using the match statement so that I only pull in metric data from B_temp that isn't already in A_table.

Sometimes, we get data for a metric for a day whose daily data has already been pulled. I.e. we do a data load on Monday that pulls in Sunday's data. However, we don't get data for Metric 17 until Tuesday and the event date for all that data is Sunday. I want the data to pull all of Sunday's data from B_temp and A_table so that all metrics are included. Right now, my code only compares the dates, so when it sees we already have Sunday's data on Tuesdays, the new Metric 17 data with Sunday as the event date gets skipped. 

Concatenate(A_table)
LOAD
* 
Resident B_temp
WHERE
1=1
AND Not match(DataKey,$(dataKeyList))
;

My match statement is failing and I cannot figure out why. I've been playing around with it on and off for weeks and cannot figure out why it is failing.

The following error occurred:
Unexpected token: 'match', expected one of: '(', 'Mid', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'not', 'bitnot', 'LITERAL_NUMBER', ...
The error occurred here:
Concatenate(A_table)
LOAD
*
Resident B_temp
WHERE
1=1
AND Not >>>>>>match<<<<<<(DataKey,110|44955,110|44956,110|44957,110|44958,110|44959,110|44960,110|44961,110|44962,110|44963,[...],9|44963)

Any suggestions on how I can fix this issue?

Additional context with mock data

A_table

MetricID	EVENT_DATE_NUM	DataKey
10	44962	10|44962

B_temp

MetricID	EVENT_DATE_NUM	DataKey
9	44960	9|44960
9	44962	9|44962
10	44960	10|44960
11	44960	11|44960
11	44962	11|44962

B_temp already has data for date 44962 (02/05/2023) but is missing data on that data for Metric 10. The final result should add in Metric 10 data for the date 02/05 without losing any other 02/05 data.

Final Result

MetricID	EVENT_DATE_NUM	DataKey
9	44960	9|44960
9	44962	9|44962
10	44960	10|44960
10	44962	10|44962
11	44960	11|44960
11	44962	11|44962

[henrikalmen · Accepted solution]

Your variable dataKeyList expands to a string like 123|44955,456|44955 (one string that includes a comma) and the match() function tries to match that full string. match(DataKey,$(dataKeyList)) expands to match(DataKey,123|44955,456|44955) (without any quotes at all, that's why it fails with an error) Instead of this that you want: match(DataKey,'123|44955','456|44955')

So you should change concat(distinct DataKey, ',') to this:

chr(39) & concat(distinct DataKey, chr(39)&','&chr(39)) & chr(39)

It creates a string that starts and ends with single quote, and the concatenation separates values with single quotes and the comma.

The reason why the match function fails completely is because your matchstring doesn't contain any quotes at all. If you had written match(DataKey, '$(dataKeyList)') it would have tried to match the whole concatenated string.

[hannahnoelle797]

That worked! Thank you!

[MarcoWedel]

see also

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...