topic Re: Changing strings to yyyy dates? in QlikView

Changing strings to yyyy dates?

Breezy — Fri, 16 Dec 2016 13:46:01 GMT

I loaded my Excel sheet which included a column of years (such as 2015, 2009, 2004, etc.). When my Excel file was loaded into Qlik the dates appear as strings, 42005, 39904, etc. I would like to turn them back into years. I think I need to define that column as yyyy. However, I have not actually used the Data Load Editor before. I just have it do everything automatically. Would I click 'unlock' first?

Below is part of the script in the LOAD section. I want to change [COMPL_PER_BEGIN_YEAR] to show just yyyy. I would like to keep the [COMPL_PER_BEGIN_DATE] as it is, however, which is 1/1/2015, 4/1/2009, etc.

[COMPL_PER_BEGIN_YEAR] AS [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE] ) AS [COMPL_PER_BEGIN_DATE]

Your assistance is appreciated. Thank you.

Re: Changing strings to yyyy dates?

sunny_talwar — Fri, 16 Dec 2016 13:50:36 GMT

May be this:

Year([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_DATE]

Re: Changing strings to yyyy dates?

Breezy — Fri, 16 Dec 2016 16:16:33 GMT

Hi Sunny,

The good news is that worked! My years are now in the format: yyyy. However, this is my first time using the Data Load Editor. When I go to Data Editor -> Associations, it will no longer allow me to associate my tables. And I need to associate Table1, column "ID" with Table 2, column "ID." I've only joined my two tables together with "Associations" before.....so I'm not sure how to do it without using Associations.... I thought since the column names are the same ("ID") that an automatic association would occur, but this is not the case.

Any tips?

Re: Changing strings to yyyy dates?

sunny_talwar — Fri, 16 Dec 2016 16:44:31 GMT

Would you be able to post a screenshot of your data model to show what issue you are running into?

Re: Changing strings to yyyy dates?

Breezy — Fri, 16 Dec 2016 17:04:04 GMT

I just noticed on my other copy of my app that if I make the association and THEN I change the script, the association maintains. However, if I change the script first, I cannot make an association (you can see that below - the PWSID will not connect - it seems like upon loading, since the column names were the same, the table on the left added the table name in front of PWSID - maybe that is why they are not automatically associating).

Re: Changing strings to yyyy dates?

sunny_talwar — Fri, 16 Dec 2016 17:10:44 GMT

Are you using Qualify *; somewhere before you load Nitrates_SellerID_2016Q3?

Re: Changing strings to yyyy dates?

Breezy — Mon, 19 Dec 2016 14:34:57 GMT

Hi,

No. In the Auto-generated section I let it be completely auto-loaded, and then I changed the part as you advised:

Year([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_DATE]

Re: Changing strings to yyyy dates?

sunny_talwar — Mon, 19 Dec 2016 14:36:36 GMT

I am talking about this table?

What field do you wish the two tables to be connected on?

Re: Changing strings to yyyy dates?

Breezy — Mon, 19 Dec 2016 14:40:19 GMT

The two tables should connect on "PWSID." But when I loaded Nitrates_SellerID_2016Q3, it modified the column name from PWSID to Nitrates_SellerID_2016Q3.PWSID, maybe to distinguish it from "PWSID" in the other table. But they won't automatically associate unless the column headings are exactly the same.

Re: Changing strings to yyyy dates?

sunny_talwar — Mon, 19 Dec 2016 14:45:10 GMT

I guess at the end of your script you can rename the field

RENAME Field Nitrates_SellerID_2016Q3.PWSID to PWSID;

Re: Changing strings to yyyy dates?

Breezy — Mon, 19 Dec 2016 15:00:52 GMT

Thank you for your assistance, Sunny