Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Breezy
Creator II
Creator II

Changing strings to yyyy dates?

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.

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Year([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_DATE]

View solution in original post

10 Replies
sunny_talwar

May be this:

Year([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_YEAR],

Date([COMPL_PER_BEGIN_DATE]) as [COMPL_PER_BEGIN_DATE]

Breezy
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

PWSID_data_model_Qlik.JPG

sunny_talwar

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

Breezy
Creator II
Creator II
Author

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?

Capture.PNG

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

Breezy
Creator II
Creator II
Author

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;