topic Re: Loading data from excel into QV: how to transform date/numeric in QlikView

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

gustavofred — Thu, 04 Feb 2021 07:14:55 GMT

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.

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

Taoufiq_Zarra — Thu, 04 Feb 2021 08:36:44 GMT

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

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

gustavofred — Thu, 04 Feb 2021 11:36:21 GMT

"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

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

Taoufiq_Zarra — Thu, 04 Feb 2021 13:02:30 GMT

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

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

gustavofred — Fri, 05 Feb 2021 09:16:20 GMT

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

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

Taoufiq_Zarra — Fri, 05 Feb 2021 09:02:40 GMT

@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

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

gustavofred — Fri, 05 Feb 2021 10:19:57 GMT

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?

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

Taoufiq_Zarra — Mon, 08 Feb 2021 07:31:11 GMT

yes @gustavofred