Questions About Where Clause and Formatting

[pedrohenriqueperna]

Hi,

I'm optimizing an API extraction script for an ERP and I've stumbled into something I'm not sure of the impact in the use of the Where clause.

Basically, I have an "Historic Base" qvd which I increment with new data that I get daily by calling this API. To filter the Base with new or updated data, I build a composed key after getting the response data, e.g:

customer_id|timestamp

The thing is, I've notice that sometimes fields appear to come in a "bad formatting". For instance, the customer_id comes as a data/timestamp value (ex. 1951-08-08T00:00:00). I understand data values are actually numbers, but I wonder if Qlik will interpret that when building composed keys, as they'd be converted to strings, right?

Lets say:

NewData:
LOAD * 
    ,customer_id&'|'&timestamp as temp_key_response
From XYZ;

In this scenario, would the key automatically be 1951-08-08T00:00:00|123456 or would it be an 18619|123456? 

In a second step, I load the the Base filtering the temp_key:

HistBase:
NoConcatenate LOAD * From ABC
Where Not Exists(temp_key_response, customer_id&'|'&timestamp);

Concatenate(HistBase)
LOAD * Resident NewData;
Drop Field temp_key_sample;

If Qlik pastes the value as it's formated (so as the timestamp) I'd have duplicates. Obviously, I did try formatting the value when building the key: 

Floor(customer_id)&'|'&Floor(timestamp)

Then in the Where clause, I just applied the formatting directly to the fields, like this:

Where Not Exists(temp_key_response, Floor(customer_id)&'|'&Floor(timestamp));

The question is: Is this approach OK? Formatting the values inside the Where clause? I'm just trying to avoid a second reload as some HistBases can be quite large.

I'm asking this because I suspect I've had some issues with duplicates, but it could be due to other reasons, as different endpoints would use different fields, and I'm dynamically assigning values to variables throughout the script. I just need to be sure I can compare values by formatting them directly in the Where clause without pointing out to a previous built key (as for the second argument).

Thanks in advance.

[hugo_andrade · Accepted solution]

Hi @pedrohenriqueperna ,

Short answer is yes. Formatting inside the Where is fine. What matters is that both sides of the comparison produce the exact same text.

Great post and explanation.

[seanbruton]

Hi @pedrohenriqueperna,

As @hugo_andrade mentioned. Yes, in short its fine.

However, it can increase compute load (bascially run slower). Instead you can try a preceding load like below. This is a nice way to run through your needs while it has the data in active memory (processing). I use this method is a lot to help normalise data in steps and faster.

NewData:
LOAD * 
Where Not Exists(temp_key_response, temp_key_response);

LOAD * 
    ,customer_id&'|'&timestamp as temp_key_response
From XYZ;

 Regards

Sean

[pedrohenriqueperna]

Thank you.

[pedrohenriqueperna]

Interesting!! Will sure try that. Thanks for the tip