Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Format Date

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

date_connection.png

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

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Format Date

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.

9 Replies

Re: Format Date

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

Not applicable

Re: Format Date

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.

date2.png

Re: Format Date

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.

Employee
Employee

Re: Format Date

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

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.

MVP & Luminary
MVP & Luminary

Re: Format Date

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.

Employee
Employee

Re: Format Date

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.

MVP & Luminary
MVP & Luminary

Re: Format Date

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.

Not applicable

Re: Format Date

Hi Rob,

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

thank you for your sugestion.

Best regards,

ted