topic Re: Strange Data Load issue in QlikView

Strange Data Load issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am facing some strange data issue in the Qlik application between the environments(DEV,QA&Prod) .

My scenario is like below

The Forecast table having a date (Fore_Cast_Date) field like "2017-08-09*",'2017-08-10',"2017-09-09*"..etc

As per requirement I need to create a report that contains " * " should be in one report and excluding " * " in other report.

Also I need to show only last 30 months of Fore_Cast_Date (which has "*") . So I am using below condition

where LEN(Fore_Cast_Date)>10 AND Fore_Cast_Date>=addmonths(PURGECHAR(Fore_Cast_Date,'*'),-30).

Which works fine in Dev & QA environemnt. But it is returning 0 records in Prod environment.

Any idea about the above issue? Let me know if you have more info.

Re: Strange Data Load issue

swuehl — Wed, 20 Sep 2017 10:45:26 GMT

Double check that your dates are properly interpreted as dates (showing a numerical representation) when loaded into Qlik. Might be that the different systems behave differently based on version / system environment.

Why don’t my dates work?

edit: I mean, something like

Fore_Cast_Date>=addmonths(PURGECHAR(Fore_Cast_Date,'*')

can only be evaluated correctly if Fore_Cast_Date itself shows a numeric representation (and then I wonder why you would need the Purgechar on the right side).

Re: Strange Data Load issue

marcus_sommer — Wed, 20 Sep 2017 10:46:54 GMT

I could imagine that Fore_Cast_Date isn't interpreted as date-value within your production environment. Take a look if the date-format settings from your main script-tab is the same in all environments. Further are you using different releases within the environments? Especially the use of the "*" wildcard within the date looked somehow odd and might part of your issue - what is the reason for it?

- Marcus

Re: Strange Data Load issue

Anonymous — Wed, 20 Sep 2017 10:51:23 GMT

Hi swuehl

Thank you for your response. Actually the Fore_Cast_Date is not coming as Date. It is string and it is Hive tables.

My question is it was working fine in Dev ,QA and it is giving 0 records in Prod alone. When we verified source data I didn't see any difference between the environment and formats.

Any Idea on this?

Re: Strange Data Load issue

Anonymous — Wed, 20 Sep 2017 10:53:12 GMT

Hi marcus_sommer

Thank you for your response. As you said the Fore_Cast_Date is not a Date and it is string and it is coming from Hive tables.

I am expecting "0" records in Dev & QA as well. Why it is working Dev & QA rather than Prod.

Any Idea on this?

Re: Strange Data Load issue

swuehl — Wed, 20 Sep 2017 10:57:14 GMT

Follow the instructions Henric is giving in his blog post I've linked to above.

Here is another one:

Get the Dates Right

Re: Strange Data Load issue

marcus_sommer — Wed, 20 Sep 2017 10:57:48 GMT

Possible reasons could be:

- different releases

- different system-variables

- different local settings within the OS

- different load-orders

- ...

Why are there wildcards within the date?

- Marcus

Re: Strange Data Load issue

Anonymous — Wed, 20 Sep 2017 10:57:54 GMT

Hi Stefan Wühl

and then I wonder why you would need the Purgechar on the right side).

Actually I need to have Fore_Cast_Date which is falling in last 30 months also it should have "*" in the

Fore_Cast_Date date field

Re: Strange Data Load issue

Anonymous — Wed, 20 Sep 2017 10:59:37 GMT

Hi marcus_sommer

Thanks for you reply. Actually it is business need that List box should have "*" added date field for selection

Re: Strange Data Load issue

swuehl — Wed, 20 Sep 2017 11:03:04 GMT

..within the last 30 months of what? Today?

Maybe

where LEN(Fore_Cast_Date)>10 AND Today(1) >=addmonths(PURGECHAR(Fore_Cast_Date,'*'),-30)

or maybe you need a different comparison

where LEN(Fore_Cast_Date)>10 AND Today(1) <=addmonths(PURGECHAR(Fore_Cast_Date,'*'), 30)

But in general I would suggest to get your dates right, see the documents I've already linked to.

Re: Strange Data Load issue

marcus_sommer — Wed, 20 Sep 2017 11:06:54 GMT

It's recommended to use only pure numeric values for any kind of matchings/calculations to avoid each potentially trouble with formattings. In your case it would mean just to add another numeric date-field to your loadings/datamodels and using the formatted ones only for displaying within the UI.

- Marcus