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

[Qlikandlearn]

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

0

4

5

10

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

Experts, any help?

Thanks,

Qlik learner.

[QFabian · Accepted solution]

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;

[Qlikandlearn · Accepted solution]

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

Thanks for your time and help 🙂