topic Re: Missing values when converting Char to NUM and adding two time fields in QlikView

Missing values when converting Char to NUM and adding two time fields

Qlikandlearn — Sat, 16 Nov 2024 17:47:33 GMT

Hi, I'm new to Qlik and have a requirement to read data from Excel then get some calculated data out of it.

One of the fields from excel file is reading by QlikView report, after applying subfield() and len() function, few of the values are not reading

Ex:

Monday	Tuesday
1h
4h	Tue 11:23
4h	3h
	3h
5h	5h

I have to add the hours to get total hours on weekly basis, I’m getting NULL when I do (Monday+Tuesday).

Then I’ve converted each field into Num by using below syntax...

If(Len(Monday)<1 or Len(Monday)>3, 0, SubField(Monday,’h’) ) as Mon,

If(Len(Tuesday)<1 or Len(Tuesday)>3, 0, SubField(Tuesday,’h’) ) as Tue

It displays

Mon	Tue
1
4	0
	3
0
5	5

Here, please notice 3h and 4h, few of the rows returned Space “NULL” instead of the number and getting different outcome when I add both fields..

Mon + Tue as total

total

Here, 3 missed from the total field and 5 should not come.

Experts, any help?

Thanks,

Qlik learner.

Re: Missing values when converting Char to NUM and adding two time fields

QFabian — Fri, 20 Nov 2020 22:05:40 GMT

Hi @Qlikandlearn please try this example, it covers the problem of time with ':', and put days into one field :

Data2:
LOAD * INLINE [
Monday, Tuesday
1h,
4h,Tue 11:23
4h,3h
,3h
5h,5h
];

Data3:
crosstable(Day, Hour)
Load
recno() as ID,
*
Resident Data2;
drop table Data2;

Data:
Load
ID,
Day,
if(FindOneOf(Hours,':')>0, Hours*24, Hours) as Hours;
Load
ID,
Day,
keepchar(Hour, '1234567890:') as Hours
Resident Data3;
drop table Data3;

Re: Missing values when converting Char to NUM and adding two time fields

Qlikandlearn — Fri, 20 Nov 2020 23:11:31 GMT

Keepchar() did the magic work here, you made my day.

Thanks for your time and help 🙂