Cleaning Excel Files in Script

[brunopaulo]

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

[brunopaulo]

Well i thought in a solution to make this work. When i do the 1st load i have null values. So before crosstable i want to check all columns and everytime is blank space i want put a 0 on it. Anyone has idea how to do this. Remember the name of columns are variable.

[YoussefBelloum]

You can do this (before the crosstable)

Table:

Load

.

.

if(isnull(Evaluation) or Len(Trim(Field))=0, 0, [Evaluation]) as Evaluation

FROM Raw table;

[brunopaulo]

I solved in a different way. Before my load and after the begin of the for each i put thos code

NullAsValue *; SET NullValue = 0;

And now null is a value and when i cross he dont remove. Thank you for the help

Regards

Bruno Paulo

[YoussefBelloum]

you are welcome,

good luck