Changing strings to yyyy dates?

[Breezy]

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.

[sunny_talwar · Accepted solution]

May be this:

Year([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_DATE]

[Breezy]

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?

[sunny_talwar]

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

[Breezy]

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

[sunny_talwar]

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

[Breezy]

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]

[sunny_talwar]

I am talking about this table?

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

[Breezy]

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.

[sunny_talwar]

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

RENAME Field Nitrates_SellerID_2016Q3.PWSID to PWSID;