qlik sql script

[mhapanka]

Hi guys,

i want to join a table (whose data I extracted from SAP HANA) to itself . The table is named tableone

A1.left: tableone

A2.right: tableone

I would like to join it to itself, only where A2."ProductStatus" is not 'To archive'

and join on A1."Product"=A2."SystemTrigger"

Also, I want to be able to name "PRODUCTDESCRIPTION" column as "System Description"

[andrey_krylov]

Hi, Mallika. Try to clarify the issue. As for "System Description" you rename fields during loading

LOAD PRODUCTDESCRIPTION  as [System Description] Resident tableone

[mhapanka]

hi, thanks for your response,

what about getting in only those values where A2."ProductStatus" is not 'To archive'

and what about the joining part?

[andrey_krylov]

Sorry. I don't totally understand, but if you need to filter values in load script

LOAD * Resident tableone

Where [A2."ProductStatus"] <> 'To archive';

[mhapanka]

what i meant to ask is, will the code that you have given, join the table to itself on A1."Product"=A2."SystemTrigger" and only where where A2."ProductStatus" is not 'To archive'

[andrey_krylov]

No.  What I'm trying to say is if you join a table to itself with the same fieldnames it won't be you want. Or maybe you mean this

A1: LOAD * Resident tableone;

left join(A1)

A2: LOAD

    [PRODUCTDESCRIPTION]  as [System Description],

    [SystemTrigger]                  as [Product]

Resident tableone

Where [ProductStatus] <> 'To archive';