topic how to change the string format ? in QlikView

how to change the string format ?

Anonymous — Wed, 15 Oct 2014 19:01:57 GMT

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.

Re: how to change the string format ?

Wed, 15 Oct 2014 19:05:15 GMT

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!

Re: how to change the string format ?

stevedark — Wed, 15 Oct 2014 19:08:53 GMT

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

Re: how to change the string format ?

Anonymous — Wed, 15 Oct 2014 19:23:58 GMT

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

Re: how to change the string format ?

Anonymous — Wed, 15 Oct 2014 19:33:01 GMT

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)

Re: how to change the string format ?

Wed, 15 Oct 2014 19:37:38 GMT

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

Re: Re: how to change the string format ?

stevedark — Wed, 15 Oct 2014 21:45:10 GMT

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

Re: Re: how to change the string format ?

Anonymous — Wed, 15 Oct 2014 23:21:47 GMT

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.

Re: how to change the string format ?

stevedark — Thu, 16 Oct 2014 07:19:12 GMT

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

Re: how to change the string format ?

Anonymous — Thu, 16 Oct 2014 23:38:27 GMT

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.

Re: how to change the string format ?

stevedark — Fri, 17 Oct 2014 08:03:01 GMT

Hi Sharma,

These kind of problems can be avoided slightly if you put a QVD layer in your application design right up front. The theory is that you can then swap out any given QVD with a QVD of data derived from a different source.

What you can do now is to create QVDs from BW that mimic the Excel sheets you already have. Swapping over in the application is then quite straight forward.

I've written a number of blog posts on data modelling and how to get your application design right. This one may be useful regarding moving to QVDs:

http://www.quickintelligence.co.uk/qlikview-qvd-files/

Hope that helps,

Steve

Re: how to change the string format ?

Anonymous — Fri, 17 Oct 2014 08:24:18 GMT

Hi steve,

Yes i have been doing 24 qvds. Is there any way i can joing 6 qvds into one big qvd.

And how can i call 2 or more qvds in an other qvw. I heard by using binary only one qvw we can load,.

And also, i have some excel sheets for last 2 years(24 months) having the same column names, whilst creating qvds i am concatenating all the 24 files as advised in the community but its a long page load script , is there any way we can shorten that , i come across for each loop but not sure as i am novice to qlikview in writing the script .I need your help in this matter.

Regards,

Sharma.

Re: how to change the string format ?

er_mohit — Fri, 17 Oct 2014 08:33:22 GMT

Please check in data source i.e excel that your date field in which form then read it in qlikview using date # function and then convert it into any format in which you want

suppose in your excel its in DD/MMM/YYYY i.e 17/Oct/2014 format.

So while loading table in qlikview

then

load

Date(Date#(DateField,'DD/MMM/YYYY'),'DD-MM-YYYY') as Date

from datasource ;

So, Output will be

17-10-2014

Re: how to change the string format ?

stevedark — Fri, 17 Oct 2014 09:39:33 GMT

Your options for joining data are CONCATENATE and JOIN. It sounds like for what you are doing - bringing data from different periods - that the concatenate option is definitely best. Binary loads are probably not something you want to be looking at here.

You certainly can enumerate around a folder of similar files to bring them all in. Take a look at this post on the subject:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

It references CSV files, but the process would be pretty much the same for Excel files.

Regards,

Steve

Re: how to change the string format ?

Anonymous — Fri, 17 Oct 2014 10:47:08 GMT

Thank You Mohit ,

I have got it now.

Re: how to change the string format ?

Anonymous — Sat, 18 Oct 2014 10:42:35 GMT

Hi steve,

I have a field called 'Paid in' which stores value 'September' in excel and now i am bringing that field into qlikview and I would like to use makedate function ,My previous developer used the function like this but

its not showing any result and I need to know what it is trying to do .

Date#(date(Makedate(num(Year),[GS3 Paid in],1))) as OBJ_GSoS_EndDate,

Can any one help me out this , How can I make it work.

Its a bit urgent pls

Regards,

Sharma.

Re: how to change the string format ?

stevedark — Sat, 18 Oct 2014 13:35:43 GMT

The makedate function takes three numbers and makes it into a date, eg:

makedate(1975,5,28) as MyDate,

If you have the string September you need to make a full string and convert to a date, like this:

Date(Date#('1 ' & [GS3 Paid in] & ' ' & Year, 'DD MMMM YYYY'), 'DD MMM YYYY') as MyDate,

The Date# converts the string to a number and the Date function formats it to make it look better.

Hope that makes sense.

Steve

Re: how to change the string format ?

Anonymous — Sat, 18 Oct 2014 14:09:34 GMT

Hi Steve,

Thanks for your reply even at weekend, much appreciated for your time.

I have tried with the above suggestion but I am getting error as <Year> Field not found.

How shall i solve it. This is bugging me

Regards,

Sharma.

Re: how to change the string format ?

stevedark — Sun, 19 Oct 2014 06:37:10 GMT

Hi Sharma,

Apologies, I presumed from your code that Year was one of the fields in your database. If you don't have the year and you just want to default to the current year you could put this:

Date(Date#('1 ' & [GS3 Paid in] & ' ' & Year(today()), 'DD MMMM YYYY'), 'DD MMM YYYY') as MyDate,

or if you want to fix the year it would be this:

Date(Date#('1 ' & [GS3 Paid in] & ' 2014', 'DD MMMM YYYY'), 'DD MMM YYYY') as MyDate,

or if the year is in a field called The Year, it would be this:

Date(Date#('1 ' & [GS3 Paid in] & ' ' & [The Year], 'DD MMMM YYYY'), 'DD MMM YYYY') as MyDate,

Hope that helps,

Steve

Re: how to change the string format ?

Anonymous — Sun, 19 Oct 2014 11:47:15 GMT

Hi Steve,

I cant help in Thanking you enough, You are the source code for any Qlikview issues.

Wondering if you do take any classes.

Regards,

Sharma.