how to change the string format ?

[Anonymous]

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.

[#2 · Accepted solution]

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!

[stevedark]

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

[Anonymous]

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

[Anonymous]

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)

[#6]

You can't reference a field named ( AS ) into the same load sentence, so you must reference to Period instead of OBJ_GDN_Date

[stevedark]

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

[Anonymous]

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.                                                                          

[stevedark]

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

[Anonymous]

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.