Creating Date Field

[Anonymous]

How can I modify the below dates so that the year is dynamic but formatting stays the same?

[1/1/2018],
[2/1/2018],
[3/1/2018],
[4/1/2018],
[5/1/2018],
[6/1/2018],
[7/1/2018],
[8/1/2018],
[9/1/2018],
[10/1/2018],
[11/1/2018],
[12/1/2018]

[Anonymous]

I don't believe so with how it's formatted. I attached an example

[bjendrick]

My guess is that whatever file is the source of your information will probably change with 2019.  Each column you have is formatted as text in the Excel file, so I'm not sure how this could be dynamic.  You might need to modify the import each year, or determine a way to only specify column letters with your Qlik import.

[Anonymous]

Correct each year the Excel file is changed manually with the new year.

I want the script to be dynamic so I don't have to keep changing it.

I tried to set

v1stMonthCurrYear= '['&'1/1/' & Year(Today())&']'

but that didn't seem to work when I pulled in the variable.

[sunny_talwar]

If the number of fields don't change and there placement don't change, you can try to work with LOAD * instead of loading with the field names?

[Qrishna · Accepted solution]

Tried to make as simple as possible and as dynamic as possible.

try this script. when the date changes to 1/1/2019 automatically you'll be having all the dates for 2019.

check out the attached too.

let vYear = year(today());

for i = 1 to 12

data:

load '[' & Date(Date#($(i) & '/1/' & $(vYear),'mm/dd/yyyy'),'mm/dd/yyyy') & ']'as Date

AutoGenerate 1;

next i

[Qrishna]

Check this qvw if you are loading from an excel file.

the year is dynamic.