Loading data from excel into QV: how to transform date/numeric

[gustavofred]

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.

[Taoufiq_Zarra]

@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 ?

[gustavofred]

"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

[Taoufiq_Zarra]

@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:

[gustavofred]

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

[Taoufiq_Zarra]

@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

[gustavofred]

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?

[Taoufiq_Zarra]

yes @gustavofred