Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
brunopaulo
Partner
Partner

Cleaning Excel Files in Script

Hello community, i have something like this

Name     |     Competence      |      Evaluation       

Bruno             Competence1               4         

Bruno             Competence2             jfkfhkahhf

Bruno             Competence3               3

Bruno             Competence4

Bruno             Competence5          30/11/2017                

Andrew          Competence1          28/11/2017        

Andrew          Competence2             hdgdgd

Andrew          Competence3              

Andrew          Competence4                    4

Andrew           Competence5                   1

And so on. In script im doing something like this

Table:

Load *

Resident TableTemp

where len(Evaluation)<2;

Im excluding the fields that doesn't mather to me but i want include blank fields. Anyone has other idea how to do this. At the moment isnt including blank fields.

Expected output

Name     |     Competence      |      Evaluation       

Bruno             Competence1                4         

Bruno             Competence3                3

Bruno             Competence4

Andrew           Competence3

Andrew           Competence4               4

Andrew           Competence5               1


Thanks in advance

Best Regards

Bruno Paulo

13 Replies
stabben23
Master
Master

Hi Bruno, whatif you trim the Field Evaluation in Your where condition? it colud be that the Field is include more than 2 blanksteps.

where len(trim(Evaluation))<2;

YoussefBelloum
Champion
Champion

Hi,

it depends on what you really have on that blank values, but you can try this:

where len(Evaluation)<2 and not isnull(Evaluation);

balar025
Creator III
Creator III

Hello,

Try this.

Load

*

Where IsNum(Evaluation) or Len(Evaluation)=0;

Load * Inline [

Name,  Competence,  Evaluation

Bruno,Competence1,  4

Bruno,Competence2,jfkfhkahhf

Bruno,Competence3,  3

Bruno,Competence4,

Bruno,Competence5,  30/11/2017 

Andrew,  Competence1, 28/11/2017

Andrew,  Competence2,hdgdgd

Andrew, Competence3,

Andrew,  Competence4,   4

Andrew,   Competence5,  1

];

brunopaulo
Partner
Partner
Author

well this solution looks perfect. But i discovered a new problem. Im doing cross table and is there where it excludes the values.

I have

Name           |          Competence 1               |      Competence2               |               Competence3

Bruno                              4                                             jshdjhd

Andrew                              5                                                                                          14/11/2017

So when i Cross table this the problem appears.

Somethings that worth a mention. I'm reading several excel files and competence can be variable depending on excel.

If you could help i apreciate. Thank you

YoussefBelloum
Champion
Champion

Try this:

where Len(Trim(Field)) > 0 and Len(Trim(Field)) < 2 and not isnull(Evaluation);

brunopaulo
Partner
Partner
Author

It gives me error because the fields arent created.

CrossTEMP2:

CrossTable (Competence, Evaluation,3)

LOAD *

RESIDENT CrossTEMP;l

So if i try do something like dat Competence and Evaluation aren't created. And fields that go to Competence column are variable, depend on excel.

YoussefBelloum
Champion
Champion

CrossTEMP2:

CrossTable (Competence, Evaluation,3)

LOAD *

RESIDENT CrossTEMP;

CrossTEMP3:

LOAD *

resident CrossTEMP2

where Len(Trim(Evaluation)) > 0 and Len(Trim(Evaluation)) < 2 and not isnull(Evaluation);


drop table CrossTEMP2;

brunopaulo
Partner
Partner
Author

Didnt solve, ill make more tests. Thank you for the help!

brunopaulo
Partner
Partner
Author

I tested only with 1 excel file and when i do cross table eliminates all null values and when i do the first load (before cross table command) the null values are there.