Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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
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
JOINLOAD
Customer_Shipper
,
SUM
(Teus) ASSUMTEUS
FROM
[..\..\Qvd\CSAV_BRASIL\MercadoECSA.qvd](qvd)
WHERE
LEN(TRIM(Customer_Shipper)) =0
OR
())
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!
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
Rgds
É 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.
Hector and Fernando,
Thank you very much for the killing tips and explanations.
U guys rock!
My best wishes!