Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help... how is the syntax to use inline table within other queries ??

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

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

Note that the quotes '''' are 4 single quote marks , not two double quote marks.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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"

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

;