topic Re: Cleaning Excel Files in Script in QlikView

Cleaning Excel Files in Script

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cleaning Excel Files in Script

stabben23 — Thu, 30 Nov 2017 10:40:08 GMT

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;

Re: Cleaning Excel Files in Script

YoussefBelloum — Thu, 30 Nov 2017 10:41:49 GMT

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);

Re: Cleaning Excel Files in Script

balar025 — Thu, 30 Nov 2017 10:46:52 GMT

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

];

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 10:50:09 GMT

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

Re: Cleaning Excel Files in Script

YoussefBelloum — Thu, 30 Nov 2017 10:56:26 GMT

Try this:

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

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 11:00:42 GMT

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.

Re: Cleaning Excel Files in Script

YoussefBelloum — Thu, 30 Nov 2017 11:12:16 GMT

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;

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 11:17:16 GMT

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

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 11:23:00 GMT

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.

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 11:39:50 GMT

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.

Re: Cleaning Excel Files in Script

YoussefBelloum — Thu, 30 Nov 2017 12:42:16 GMT

You can do this (before the crosstable)

Table:

Load

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

FROM Raw table;

Re: Cleaning Excel Files in Script

brunopaulo — Thu, 30 Nov 2017 12:57:45 GMT

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

Re: Cleaning Excel Files in Script

YoussefBelloum — Thu, 30 Nov 2017 13:13:14 GMT

you are welcome,

good luck