Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be like this
LOAD Date,
Class,
Grade,
Teacher,
Notes
FROM ...
Where Len(Trim(Grade)) > 0 and Len(Trim(Teacher)) > 0;
Thanks Sunny.
I tried your method, Chemisty still shows up.
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;
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?
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
file attached
tried same result. i attached the file.
any clue as to why it won't work?
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 <>'-';