Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I got a Inline Table called "Tipo_Documento" and I would like to use it to check a load of a Table called "Documenti st".
QlikSense do not recognize the table name as I wrote it.
using this sintax nothing works... ;-(
FROM "Documenti st"
LEFT JOIN Tipo_Documento ON "Documenti st"."Tipo Doc" = Tipo_Documento."Tipo Doc"
WHERE "Documenti st".Anno=2015
AND "Documenti st"."Tipo Doc" = Tipo_Documento."Tipo Doc"
How is the right way and syntax to use the name of a Inline table within other query
Tipo_Documento:
LOAD * Inline [
"Tipo Doc" , Descriz_Tipo_Doc
F1,F1 Fattura
F2,F2 Fatt. Immed.
C1,C1 Nota accredito
CPA,CPA Nota Accr. PA
FPA,FPA Fattura
F11,F11 Fattira diff.ta
F21,F21 Fattura accomp.
];
LIB CONNECT TO "Dinamico_Eos_BI_Local";
//Buffer(incremental)
LOAD "Id doc" ,
"N doc",
"Tipo Doc",
Anno,
"Id pagamento",
"Id anag",
date(Data, 'DD-MM-YYYY') as Data_Ft,
Month(Data) As Mese_Ft,
Year(Data) As Anno_Ft,
Day(Data) As Giorno_Ft ,
'Q-' & Ceil(Month(Data)/3) As Q,
Stato,
"Note",
if (left("Tipo Doc",1)='C' , -TotImp , TotImp) as TotImp,
if (left("Tipo Doc",1)='C' , -TotNI , TotNI ) as TotNI,
if (left("Tipo Doc",1)='C' , -TotIva , TotIva ) as TotIva,
if (left("Tipo Doc",1)='C' , -Totdoc , Totdoc ) as Totdoc,
CodDiv,
Cambio,
CodAg,
CausVendita;
SQL SELECT "Id doc",
"N doc",
"Tipo Doc",
Anno,
"Id pagamento",
"Id anag",
Data,
Stato,
"Cod iva",
"Documenti st"."Note",
TotImp,
TotNI,
TotIva,
Totdoc,
CodDiv,
Cambio,
CodAg,
CausVendita
FROM "Documenti st"
LEFT JOIN Tipo_Documento ON "Documenti st"."Tipo Doc" = Tipo_Documento."Tipo Doc"
WHERE "Documenti st".Anno=2015
AND "Documenti st"."Tipo Doc" = Tipo_Documento."Tipo Doc"
;
The query after the SQL statement is not executed by QV - it is handed off to the DB server. The server has no knowledge of the QV inline table. You will need to perform this join in QV after loading the data.
Or you can concatenate the values of Tipo_Documento."Tipo Doc" and use
-- before the load statement
T_Concat:
LOAD '''' & Concat([Tipo Doc], ''',''') & '''' As AllTipoDoc
Resident Tipo_Documento;
Let vAllTipoDoc = Peek('AllTipoDoc');
DROP Table T_Concat;
-- in the load
FROM "Documenti st"
WHERE "Documenti st".Anno=2015
AND "Documenti st"."Tipo Doc" In ($(vAllTipoDoc ));
(If Tipo Doc is numeric, you won't need the enclosing quotes)
Note that the quotes '''' are 4 single quote marks , not two double quote marks.
Hello Jonathan, thanks.
Why your post is not editable to copy your code ?
I'm new to Qlik Sense ;-(
So , first I have to trasform inline into a Resident table T_Concat
the second step is almost obscure...
Let vAllTipoDoc...
what does it do ?
Is it not possible to write something like this ...
LEFT JOIN Resident [Tipo_Documento] ON "Documenti st"."Tipo Doc" = Resident [Tipo_Documento]."Tipo Doc"
WHERE "Documenti st".Anno=2015
AND "Documenti st"."Tipo Doc" = Resident [Tipo_Documento]."Tipo Doc"
It went into moderation so that i had to send an image - its to do with spam attacks here. When the original post comesout of moderation, that will be normal editable content. See attachment.
OKey !!!
The syntax of LEFT JOIN line how has to be done ?
FROM "Documenti st"
LEFT JOIN Resident [Tipo_Documento] ON "Documenti st"."Tipo Doc" = Resident [Tipo_Documento]."Tipo Doc"
WHERE "Documenti st".Anno=2015
AND "Documenti st"."Tipo Doc" In ($(vAllTipoDoc ))
;