topic Re: Format Date in QlikView

Format Date

Tue, 09 Jun 2015 02:06:56 GMT

Hi all,

I have a file qvw connected to an excel file. but when I display the date (date_t), this date_t can not connect with field sales (sales and date_t in same table).

I've made the setting at the beginning of the script.

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

How to set 'date_t' to connect with sales?

hope someone help me.

Best regards,

Ted

Re: Format Date

settu_periasamy — Tue, 09 Jun 2015 02:15:48 GMT

Hi,

Can you scroll down the ListBox (date_t) and check it.. some times the data appear in the bottom.

or goto ListBox Properties -> Sort -> Put State as Ascending..

Re: Format Date

Tue, 09 Jun 2015 02:33:23 GMT

hi settu,

i have sorting the date_t, but there is not connection with sales. sales is from other file (txt file) and date_t is from excel file. but i put in same table.

Re: Format Date

settu_periasamy — Tue, 09 Jun 2015 02:38:18 GMT

Hi,

In the script, try to change the date_t format like

Date(Date, 'DD/MM/YYYY') as date_t

if it is not working, please provide the sample data.

for your reference find the attachment.

Re: Format Date

ToniKautto — Tue, 09 Jun 2015 03:10:01 GMT

My best guess is that your data is not properly associated. This could happen for many reasons, and a sample would make it a lot easier to analyse.

Please attach a sample QVW file and perhaps also a copy of the source XLS file.

Re: Format Date

settu_periasamy — Tue, 09 Jun 2015 03:24:51 GMT

Hi ted,

Actually, there is no Sales Data for that field date_t. Please follow the Toni's suggestion.

for your information, i attached the excel file (I mentioned the yellow color columns for there is no sales data associated with date_t) from Sales Table.

Re: Format Date

jagan — Tue, 09 Jun 2015 03:35:24 GMT

Hi,

Try like this, you need to format the data

Data1:

LOAD

Date(date_t) AS date_t,

FROM DataSource1;

Data2:

LOAD

Date(date_t) AS date_t,

FROM DataSource2;

Hope this helps you.

Regards,

Jagan.

Re: Format Date

ToniKautto — Tue, 09 Jun 2015 03:46:57 GMT

The first thing I think you should do is to eliminate the synthetic keys that you currently have. This will make your data model a bit more solid and enable better reliability for your aggregations.

I noticed that you are currently using QlikView 9. This is no longer a supported versions, so I would like to suggest that you start planning for a migration to QlikView 11.20 SR11.

Finally your question about date_t and sales filed not being associated properly. If you put these fields into a table box, and clear the selections, you will see that then relation between the various values. My guess is that you have just concatenated the data, and there by missed to combine data rows. Perhaps joining data between your various sources would give you the relation you are looking for.

The sample is way too complex for me to understand exactly what you are doping are what the intention is, so you will have to limit the load and troubleshoot each phase to see where your logic breaks.

Re: Format Date

rwunderlich — Tue, 09 Jun 2015 04:19:37 GMT

More importantly than formatting the date, first get rid of the duplicate fields that are not matching up between the tables. Remove these fields from the sales table.

Month_n

monthnum

Year_n;

Those fields should only be in the TimeLine table. And the link between TimeLine and sales should only be one field, "date".

-Rob

P.S. You guys really should upgrade from V9.

Re: Format Date

Tue, 09 Jun 2015 06:06:15 GMT

Hi Rob,

you are right, I am too much use of duplicate fields.

thank you for your sugestion.

Best regards,

ted