topic Re: Help... how is the syntax to use inline table within other queries ?? in Management & Governance

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

Tue, 29 Sep 2015 08:59:41 GMT

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

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

jonathandienst — Tue, 29 Sep 2015 09:07:29 GMT

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)

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

jonathandienst — Tue, 29 Sep 2015 09:13:39 GMT

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

jonathandienst — Tue, 29 Sep 2015 09:15:02 GMT

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

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

Tue, 29 Sep 2015 09:34:42 GMT

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"

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

jonathandienst — Tue, 29 Sep 2015 09:45:12 GMT

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.

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

Tue, 29 Sep 2015 09:57:31 GMT

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

;