Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have a date field(Date) as OBJ_GDN_Date which is coming from excel but when i am loading the date field to qlikview it is displaying as 41729.
Can any one help me how to change it Date format.
Regards,
Sharma.
Use Date(OBJ_GDN_Date) in Qlik to see it in Date format
41729 is a format that qlik use to store dates. This is the quantity of days since the seed (1990)
Regards!
Use Date(OBJ_GDN_Date) in Qlik to see it in Date format
41729 is a format that qlik use to store dates. This is the quantity of days since the seed (1990)
Regards!
Hi Sharma,
You should also provide a format specifier with the date function, like this:
Date(OBJ_GDN_Date, 'DD MMM YYYY')
Typically I will put the format I use in a variable at load, like this:
let vDateFormat = 'DD MMM YYYY';
So that I can refer to that in the date function like this:
Date(OBJ_GDN_Date, '$(vDateFormat)')
The best place to do the formatting is at load time, so in your load script you would have this:
Date(OBJ_GDN_Date, '$(vDateFormat)') as OBJ_GDN_Date,
Hope that helps,
Steve
Hi Steve,
It is still showing error.
set DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
let vDateFormat = 'DD MMM YYYY';
Gdn:
LOAD
Period as OBJ_GDN_PERIOD,
Date(OBJ_GDN_Date, 'DD MMM YYYY') ,
Date(OBJ_GDN_Date, '$(vDateFormat)'),
Network as OBJ_GDN_NETWORK,
[Overall Satisfaction Scores] as OBJ_GDNOverallscore,
Rank as OBJ_GDN_Rank
Date(OBJ_GDN_Date, '$(vDateFormat)') as OBJ_GDN_Date
Hi steve,
This is the error it is throwing ,I have given exactly what you have mentioned in the load script .
Can you pls help me.
Regards,
Sharma.
Field not found - <OBJ_GDN_Date>
Gdn:
LOAD
Period as OBJ_GDN_PERIOD,
Network as OBJ_GDN_NETWORK,
[Overall Satisfaction Scores] as OBJ_GDNOverallscore,
Rank as OBJ_GDN_Rank,
Date(OBJ_GDN_Date, 'DD MMM YYYY') as OBJ_GDN_Date
FROM
(ooxml, embedded labels, header is 1 lines, table is Sheet1)
You can't reference a field named ( AS ) into the same load sentence, so you must reference to Period instead of OBJ_GDN_Date
Apologies, I thought OBJ_GDN_Date was the name in the source file. If the field is called Date it should read:
Date(Date, 'DD MMM YYYY') as OBJ_GDN_Date
If the field is called something else that should go where the highlighted field is.
Hope that helps,
Steve
Excellent Steve, you are a star. Your videos carry abundance of knowledge.
Parallely, I have a question that I want to set a config file which sit outside the qlikview where users will update their files for an example like set their targets and that target should reflect at qvd level and when that qvd is loaded the dashboard items should pick the right target. I need your expertise how would I do that ....Any code to share pls.
Much oblige,
Sharma.
The simplest way to have a configuration file extremal to the main QVW is
to have an Include file. If you Google for Must_Include you will find
details of how to implement this.
However, include files are only useful if the people you need to update
then are not afraid of source code.
The other option is an Excel spreadsheet with the configuration in. You
then need to load that into a temporary table and peek out the values you
need into variables.
This can work well as you can enumerate around a number of rows.
I will often have a spreadsheet with columns of variable name and variable
value and then loop around creating each variable.
Hope that helps.
Steve
Thank you Steve. Your sharing of knowledge is indeed invaluable.
Well , I need your piece of advice. I am currently doing a project where the dashboard was developed using excel sheets for a particular year and now I need to use the same dashboard but , database is coming from SAP BW , I wont be changing much at dashboard items but I need to disconnect all the excel connections and connect with SAP BW source. I know its easier said than done as there are many dependency objects if i change anything.
I have tried some of those but my project deadlines are a bit sharp. so i am a bit lost how can I speed up this .I need your expertise and faculties to make myself clear in the approach.
Thank you very many,
Sharma.