Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, im getting an excel file, which i have to load into QV. In the first column i have the calendar week with the year. For some reason i also have this date in numeric format in this excel file. How would i transform the date, so all data looks the same: like 07-2021
my idea:
Date(Num(DATE_VALUES), 'MM-YYYY') as WEEK_YEAR
my idea works only for one part of the values.
@gustavofred can you elaborate
44256->01/03/2021(DD/MM/YYYY)->week 9-2021 ? and 2-2021 is Month or week 2 ?
how you get 07-2021 ? from those dates ?
"how you get 07-2021 ? from those dates ?"
thats just how i want the formatting to be, thats not an actual date. i want it to be MM-YEAR.
"44256->01/03/2021(DD/MM/YYYY)->week 9-2021" -> yes i want to convert a number like 44256 into 09-2021
"and 2-2021 is Month or week 2 ?" and i want to convert 2-2021 into 02-2021
so everything is in the same format
@gustavofred you can use alt funtion
for example to convert all to date format you can use :
Date(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1))))))
or add other format like this :
Data:
load *,Num(Week(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))),'00')&'-'&Year(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))) as NewDateField inline [
Date
2-2021
44256
];
output:
Thank you
i your code with addinal date information:
Data:
load *,Num(Week(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))),'00')&'-'&Year(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))) as NewDateField inline [
Date
1-2021
2-2021
3-2021
4-2021
5-2021
6-2021
7-2021
8-2021
9-2021
10-2021
11-2021
12-2021
46256
44256
45256
43256
41256
53256
40256
];
Can you explain how im getting results for null values for the formula (red circle)?
@gustavofred can yo share your sample normaly if its null the result is null (I have just tested)
or you can add
if(len(trim(Date))>0,Num(Week(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))),'00')&'-'&Year(Floor(Alt(Num(Date), Num(MakeWeekDate(subfield(Date,'-',2), subfield(Date,'-',1)))))),'') as NewDateField
its exactly the data i posted above. Thank you, the new code is working as expected. The only thing you added is, that it should define the code, if the length of our date is >0 right?
yes @gustavofred