Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlikandlearn
Contributor III
Contributor III

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

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.

 

Labels (9)
2 Solutions

Accepted Solutions
QFabian
Specialist III
Specialist III

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;

QFabian

View solution in original post

Qlikandlearn
Contributor III
Contributor III
Author

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

Thanks for your time and help 🙂

View solution in original post

2 Replies
QFabian
Specialist III
Specialist III

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;

QFabian
Qlikandlearn
Contributor III
Contributor III
Author

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

Thanks for your time and help 🙂