Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
brianna_in_dc
New Contributor III

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

Re: Changing strings to yyyy dates?

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

Re: Changing strings to yyyy dates?

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

brianna_in_dc
New Contributor III

Re: Changing strings to yyyy dates?

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?

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

brianna_in_dc
New Contributor III

Re: Changing strings to yyyy dates?

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

Re: Changing strings to yyyy dates?

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

brianna_in_dc
New Contributor III

Re: Changing strings to yyyy dates?

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?

I am talking about this table?

Capture.PNG

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

brianna_in_dc
New Contributor III

Re: Changing strings to yyyy dates?

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?

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

RENAME Field Nitrates_SellerID_2016Q3.PWSID to PWSID;