Format Date

[#1]

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

[settu_periasamy]

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..

[#3]

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.

[settu_periasamy]

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.

[ToniKautto]

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.

[settu_periasamy]

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.

[jagan]

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.

[ToniKautto]

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.

[rwunderlich · Accepted solution]

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.

[#10]

Hi Rob,

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

thank you for your sugestion.

Best regards,

ted