topic Full load order by index in Qlik Replicate

Full load order by index

MoeE — Tue, 10 Oct 2023 05:33:23 GMT

Hi,

I recently read through this article which showed how we can get the full load to select the table in order of index in order to avoid duplicates. https://community.qlik.com/t5/Official-Support-Articles/Handling-Duplicate-Rows-when-doing-a-Full-Load-in-Replicate/ta-p/1712074

I think I've followed all the steps correctly however I can't seem to get the syntax to parse. When I click parse nothing happens and no messages appear. (Success nor fail). I can't click ok either because nothing happens.

I played with the syntax a bit more and found that this statement allows me to parse successfully however I'm not sure if this achieves the same thing. Can someone please confirm? Thank you.

Kind regards,

Mohammed

Re: Full load order by index

sureshkumar — Tue, 10 Oct 2023 06:07:30 GMT

Hello @MoeE

Add the filter directly into the Record Selection Condition [Instead of adding it in Expression Builder] Click okay, then save your task.

--FILTER: 1=1) ORDER BY $AddressIDs

Regards,
Suresh

Re: Full load order by index

john_wang — Tue, 10 Oct 2023 15:40:29 GMT

Hello @MoeE ,

Looks like the article is out of date. I will try to amend it when I have time.

Now you can follow below steps:

1. Go to Replicate console Design perspective window

2. Choose the table, press the left <Ctrl> key, and click <Table Settings> button

3. Then you will get the "Fullload Passthru Filter" window in Filter area

4. Add the below expression in Fullload Passthru Filter input area:

1=1 ) ORDER BY ID,NAME --

where ID,NAME is the columns name you want to order by. please take note there are two minus signs at the end of the expression.

Regards,

John.

Re: Full load order by index

MoeE — Wed, 11 Oct 2023 00:03:50 GMT

Hi John,

Thanks for the response.

1=1 ) ORDER BY $AddressID -- doesn't seem to work at all whether I put it in the Passthru filter or the Record Selection Condition

Removing the parentheses like this, "1=1 ORDER BY $AddressID --", or adding another parentheses likes this, "(1=1) ORDER BY $AddressID --", lets me run the full load with no errors. However this only works when I enter it into the Record Selection Condition only but not the Passthru Filter.

1. Do you know if my new syntax is still doing the order by chosen column? e.g. will it do the same thing as

"1=1 ) ORDER BY ID,NAME --" ?

2. It also seems to run (in Record Selection Condition) with without the two minus signs at the end. What is the purpose of those two signs? "1=1 ORDER BY $AddressID"

Regards,

Mohammed

Re: Full load order by index

john_wang — Wed, 11 Oct 2023 01:19:59 GMT

Hello @MoeE ,

Thanks for your feedback.

I have a detailed description of the expression syntax, see my comment in Qlik Replicate | Custom SQL.

Regarding your doubts:

1=1 ) ORDER BY $AddressID -- doesn't seem to work at all whether I put it in the Passthru filter or the Record Selection Condition

I think the column name is incorrect - unless in the database table the column name contains "$".

In short, the column name presents here is exactly the same name as the table because the expression in the Passthru filter will be delivered to the source database for executing as is.

2. It also seems to run (in Record Selection Condition) with without the two minus signs at the end. What is the purpose of those two signs? "1=1 ORDER BY $AddressID"

I'm not sure what's the Replicate version you are running now. The expression syntax was changed a little between different versions. The expression samples I provided today work in Replicate 2022.11 & 2023.5.

If you hit any error, please turn SOURCE_UNLOAD to Verbose, and provide the few of lines (with the SQL statements line and error messages line) I'd love to work with you together to fix it.

Good Luck,

John.

Re: Full load order by index

Heinvandenheuvel — Wed, 11 Oct 2023 22:09:35 GMT

>> 1=1 ) ORDER BY $AddressID -- doesn't seem to work at all whether I put it in the Passthru filter

Duh... it is a PASSED THRU and $AddressID is a Replicate variable, not a source DB column.

Replicate just adds a WHERE clause to the source SELECT.

Seems to me you need to drop that closing parenthesis to make it a valid SQL where close.

No Quotes.

If you still have trouble share the relevant chunk(s) from the exported task JSON

Better still, run with a single tiny test table only and LOGGING for SOURCE_UNLOAD set to debug or VERBOSE

Check the log to see if you can spot the issue yourself and share (parts) if help is still needed.

Hein.

Re: Full load order by index

MoeE — Wed, 11 Oct 2023 22:30:19 GMT

Hi John, Hi Hein,

Thank you for the help. My source is MySQL, after analysing the logs I found that the "--" was not commenting out the final parentheses ")". I replaced it with a "#" instead and it worked successfully.

My final statement was "1=1) ORDER BY ProductID; #".

Thanks for the help.

Regards,

Mohammed

Re: Full load order by index

shashi_holla — Wed, 11 Oct 2023 22:32:23 GMT

I agree with both @john_wang and @Heinvandenheuvel

This is the trick to Passthrough filter :

Build the query (select * table where date > sysdate) in the source DB, run and verify whether it fetches the required data and copy everything after where clause and paste it in the FullLoad Passthru Filter field.

Re: Full load order by index

john_wang — Thu, 12 Oct 2023 04:00:34 GMT

Hello @MoeE ,

Thank you for the feedback.

My final statement was "1=1) ORDER BY ProductID; #".

You are right! the sub-clause will be delivered to source database, and certainly the syntax is different between variable databases. (for DB2, the comment off sign is "--"; for MySQL it's "#" etc).

Best,

John.