topic Re: Multiple Where clause & data issue in QlikView

Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 19:45:02 GMT

I have data in following format:

Date	Class	Grade	Teacher	Notes
2017-05-28	English	5	-	dfadf
2017-05-29	Math	6	Ms Walsh	dafd
2017-05-20	Science	10	Ms Helen	dafdaf
2017-05-14	Chemistry	-	Mr. Tom

I want to create 2 where clause where i can look for:

1) during load script, delete the row where Grade is Null.

2) during load script, delete the row where Teacher is null.

Date	Class	Grade	Teacher	Notes
2017-05-29	Math	6	Ms Walsh	dafd
2017-05-20	Science	10	Ms Helen	dafdaf

Re: Multiple Where clause & data issue

sunny_talwar — Tue, 30 May 2017 19:46:39 GMT

May be like this

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM ...

Where Len(Trim(Grade)) > 0 and Len(Trim(Teacher)) > 0;

Re: Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 19:50:20 GMT

Thanks Sunny.

I tried your method, Chemisty still shows up.

Re: Multiple Where clause & data issue

sunny_talwar — Tue, 30 May 2017 19:53:36 GMT

Seems to work for me using Inline table load here:

Table:

LOAD * INLINE [

Date, Class, Grade, Teacher, Notes

2017-05-28, English, 5, , dfadf

2017-05-29, Math, 6, Ms Walsh, dafd

2017-05-20, Science, 10, Ms Helen, dafdaf

2017-05-14, Chemistry, , Mr. Tom

]

Where Len(Trim(Grade)) > 0 and Len(Trim(Teacher)) > 0;

Re: Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 19:55:33 GMT

i am doing it from excel. only difference i see between Empty Teacher vs Grade is that:

Teacher = blank

Grade = -

would that cause and issue?

Re: Multiple Where clause & data issue

sunny_talwar — Tue, 30 May 2017 19:57:47 GMT

It could very well be... there might be a character which is different from a space... may be try this

Table:

LOAD * INLINE [

Date, Class, Grade, Teacher, Notes

2017-05-28, English, 5, , dfadf

2017-05-29, Math, 6, Ms Walsh, dafd

2017-05-20, Science, 10, Ms Helen, dafdaf

2017-05-14, Chemistry, , Mr. Tom

]

Where Len(Trim(Grade)) > 0 and Len(Trim(KeepChar(Upper(Teacher), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) > 0;

or something similar

Re: Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 19:59:59 GMT

file attached

Re: Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 20:11:40 GMT

tried same result. i attached the file.

Re: Multiple Where clause & data issue

userid128223 — Tue, 30 May 2017 20:52:13 GMT

any clue as to why it won't work?

Re: Multiple Where clause & data issue

Digvijay_Singh — Wed, 31 May 2017 00:06:30 GMT

Looks lilke you got text char '-' inside grade -

This works but you may need to understand what this char means.

Data:

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM

where.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(Grade)) > 0 and Len(Trim(Teacher)) > 0 and Grade <>'-';

Re: Multiple Where clause & data issue

sunny_talwar — Wed, 31 May 2017 02:35:36 GMT

May be something like this for your Excel

Table:

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM

where.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(KeepChar(Grade, '0123456789'))) > 0 and

Len(Trim(KeepChar(Upper(Teacher), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) > 0;

Table:

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM

where.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(KeepChar(Grade, '0123456789'))) > 0 and Len(Trim(Teacher)) > 0;