topic how to add a filter for a integer column with date value as 20220132 in Qlik Replicate

how to add a filter for a integer column with date value as 20220132

jyeragi — Fri, 06 Oct 2023 17:04:09 GMT

I have column value (represents date) "20220132" added as integer in a sql table. I need to filter that data in QLIK replicate but need a portion of data in the filter clause.... currently I am using substr. but is there a better way to do it?

Filter the data for left(columnvalue) = 2023

Re: how to add a filter for a integer column with date value as 20220132

Heinvandenheuvel — Fri, 06 Oct 2023 21:24:12 GMT

Substring is fine if you just need to filter by year, but then you should compare against '2023' - quoted! Test.

Personally I would used $DateAsInt/10000 == 2023 as integer source divides in Replicate remain INTEGER.

Now if you also need to validate the string to exclude invalid dates like you presented (accidently) in your example "20220132", or if you need to convert to a proper date (highly recommended!) then you need to take it further apart as strings or with DIV and MOD operations as there is no format specifier for string to dat conversions.

Mind you, the documentation for MOD and DIV in the Replicate User Guide is weak. There is a typo in MOD (%SALARY/7) should read (SALARY%7 or rather: $SALARY % 7). And the bit about decimal points for DIV is incomprehensive. You best bet is to run a few interactive test with the Expression Builder PARSE - TEST.

Have fun,

Hein.

Re: how to add a filter for a integer column with date value as 20220132

john_wang — Sat, 07 Oct 2023 08:43:34 GMT

Hello @jyeragi ,

Besides @Heinvandenheuvel 's comment, I'd like to use $DateAsInt > 20230000 straightly, without implicit/explicit data type conversations, and no additional computing.

Regards,

John.

Re: how to add a filter for a integer column with date value as 20220132

jyeragi — Tue, 24 Oct 2023 22:41:38 GMT

for the following script

Filter the data for left(columnvalue) = 2023

can we fetch the right side values (e.g: 2023) as max from a table name?

I am trying to fetch max of date

tsql query : Select max(snapshot_month_dt) from dateList table.... where snapshot_month_dt being int column which stores values like 20230930, 20230831

Re: how to add a filter for a integer column with date value as 20220132

john_wang — Wed, 25 Oct 2023 01:13:15 GMT

Hello @jyeragi ,

If the data type is INT then I think @Heinvandenheuvel provided a good way:

Personally I would used $DateAsInt/10000 == 2023 as integer source divides in Replicate remain INTEGER.

If the data type is STRING then substr() can be used.

Regards,

John.

Re: how to add a filter for a integer column with date value as 20220132

jyeragi — Wed, 25 Oct 2023 01:56:23 GMT

Thanks John.... I have verified and $DateAsInt/10000 = 2023 works fine....The issue is now, I need to replace that value with value from database table by getting the max value....

snapshot_month_dt >= max(snapshot_month_dt)

How can I adjust the following tsql queries in Fullload Passthru Filter format?

Select max(snapshot_month_dt) from dbo.DateList

OR
Select top 1 snapshot_month_dt from dbo.DateList where snapshot_month_dt in (select distinct max(snapshot_month_dt) from dbo.DateList)

where snapshot_month_dt is in 20230930 format

Re: how to add a filter for a integer column with date value as 20220132

jyeragi — Wed, 25 Oct 2023 23:40:18 GMT

@john_wang - Do you have any examples on the above query/problem?

Re: how to add a filter for a integer column with date value as 20220132

Heinvandenheuvel — Thu, 26 Oct 2023 21:46:14 GMT

@jyeragi - "need to replace that value with value from database table by getting the max value"

Well, when using that for CDC you will need to use SOURCE_LOOKUP. Here is my (working, tested, silly) example. In this contrived example I just want to take the last 3 rows based on an ever increasing ID field. The filter expression I used for that was: $ID > source_lookup(999,'ATT_USER','TEST','MAX(ID)','1=1') - 3

You could try $snapshot_month_dt >= source_lookup(999,'your_schema','your_table','MAX(snapshot_month_dt )','1=1')

For pass-thru fullload filter you need to use a SOURCE-DB NATIVE select clause. Look for other articles on guidance how to do so.

Hein.