topic Filtering/Comparing Null/Blank String Fields in QlikView

Filtering/Comparing Null/Blank String Fields

Wed, 05 Jan 2011 13:20:51 GMT

Greetings gentlemen and ma'ams!!!

I am trying to bring only fields that are not blank or null during a LOAD / LEFT JOIN statement, but unsuccesfully.

The code follows below:

LOAD

Customer_Shipper

SUM

(Teus) AS

SUMTEUS

FROM

[..\..\Qvd\CSAV_BRASIL\MercadoECSA.qvd](qvd)

;

LEFT

JOIN

LOAD

Shipper

AS Costumer_Shipper

[Partner Code]

FROM

[..\..\Datos Externos\CSAV_BRASIL\dicionario mercado-csav\dicionario.xls] (biff, embedded labels, table is Sheet1$

)

WHERE

NOT EXISTS(Costumer_Shipper,''

)

GROUP

BY Customer_Shipper,

[Partner Code];

and the following errors are dysplayed after running the script:

... as far as I can see, my code doesn't seem to be out of logic, and I just can't figure out what's wrong...

Could someone shed some light upon this, PLEASE???

In advance, my apologies if this is an seelty question (official noob here).

Thx!!!

Filtering/Comparing Null/Blank String Fields

fernandotoledo — Wed, 05 Jan 2011 13:41:12 GMT

Olá Thiago, parece que você usou o Group By no comando load errado. Você só usaria nos comandos Load que tiverem alguma agregação (Sum, Max, Min, Count, Avg, ...).

Para trazer "não nulos" costumo fazer a condição

WHERE LEN(TRIM(CAMPO)) = 0

Regards,

Fernando

Translation:

You mustn´t use group by clauses in a load statement unless you use an agregation function (such sum, avg, count, ...)

To filter null values I use the expression

WHERE LEN(TRIM(FIELD)) > 0

Filtering/Comparing Null/Blank String Fields

Wed, 05 Jan 2011 14:37:34 GMT

Fernando, valeu pela resposta!

Alterei o codigo, mas ainda to tomando erro:

Dicionario_VS_Market:

LOAD

Shipper

AS Costumer_Shipper

[Partner Code]

FROM

[..\..\Datos Externos\CSAV_BRASIL\dicionario mercado-csav\dicionario.xls] (biff, embedded labels, table is Sheet1$)

;

RIGHT

JOIN

LOAD

Customer_Shipper

SUM

(Teus) AS

SUMTEUS

FROM

[..\..\Qvd\CSAV_BRASIL\MercadoECSA.qvd](qvd

)

WHERE

LEN(TRIM(Customer_Shipper)) =

LEN(TRIM(Customer_Shipper)) = (Null

())

GROUP

BY Customer_Shipper,

[Partner Code];

Nao acho o erro na minha sintaxe (o QV nao eh bem específico quanto aos erros no código...).

Idéias?

Um forte abraço!

----------------------------------------------

Translation:

Fernando, thanx for the answer!

I altered the code, but there still an error on it:

--- same code as above ---

Just can't find my mistake in the code (QV is not very especific about code errors...)

Thoughts?

My best wishes!

Filtering/Comparing Null/Blank String Fields

hector — Wed, 05 Jan 2011 14:42:46 GMT

The problem is that Partner Code doesn't exist in the second table, so you are unable to group by it

You need to change the logic of the load

Load and sum() the first table
Load the second table and join to the first one
Load from resident the first table and redo the group by, so Customer_Shipper and Partner Code will be part of the new table
drop tables or use noconcatenate to avoid auto concatenation

Rgds

Filtering/Comparing Null/Blank String Fields

fernandotoledo — Wed, 05 Jan 2011 14:53:35 GMT

É que o Load funiona diferente do Select de SQL. No qlik você precisa juntar as tabelas numa Tabela auxiliar, ler da tabela auxiliar fazendo a agregação (o que cria uma nova tabela, já a final) e depois um "Drop" nela.

Veja o exemplo que vai ficar claro como funciona:

table_temp:
LOAD
 A,
 B,
 C
FROM FILE1.QVD (qvd);

RIGHT JOIN LOAD
 B,
 D
FROM FILE2.QVD (qvd);

final_table:
LOAD
 A,
 B,
 SUM(C) AS C_new,
 D
RESIDENT table_temp
GROUP BY A,B,D;

DROP TABLE table_temp;

Abraço,

Fernando

Translation

See the code above to understand how to join two tables and then agregate the data. It´s quite different from SQL´s Select.

Filtering/Comparing Null/Blank String Fields

Wed, 05 Jan 2011 17:33:13 GMT

Hector and Fernando,

Thank you very much for the killing tips and explanations.

U guys rock!

My best wishes!