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.
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.
edit: I mean, something like
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).
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?
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?
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?
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?
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
..within the last 30 months of what? Today?
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.