Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
Specialist

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

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
Specialist

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

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

Thanks for your time and help 🙂

View solution in original post