Joins in Qlikview!!

[#1]

COULD ANYONE PLEASE EXPLAIN THIS CODE??

qualify *;

unqualify CANCL_AMT_KEY;

A:
Directory;LOAD
     SrNo,
     [Recorded year month]&[Recorded year month]&[Sales order] as CANCL_AMT_KEY,
     Product,
     [Sales order],
     [Adjusted rental amount based on account receivable amount],
     [Cancellation flag]
;//replacewith SQL start      Directory;
       LOAD * FROM testdata.xls
       (biff, embedded labels, table is CANCL_AMT$);//replacewith SQL end
B:Left join (A)LOAD
     SrNo,
     [Recorded year month]&[Recorded year month]&[Sales order] as CANCL_AMT_KEY,
     [Recorded year month]+1 as [Recorded year month],
     Product,
     [Sales order],
     [Adjusted rental amount based on account receivable amount],
     [Cancellation flag]
;//replacewith SQL start      Directory;
       LOAD * FROM testdata.xls
       (biff, embedded labels, table is Temp$);//replacewith SQL endunqualify *;

RESULT_TABLE:LOAD 
     A.SrNo,
     A.Product,
     [A.Sales order],
     [A.Adjusted rental amount based on account receivable amount] - [B.Adjustedrental amount based on account receivable amount] as [Adjusted rental amount based onaccount receivable amount],
     [A.Cancellation flag] RESIDENT A;
drop table A;

[jagan]

Hi,

In the above script,

1. Initially Table A is loaded

2. Then Table B is loaded and left joined to table A.

3.  Finally the required columns from the above table A is loaded into RESULT_TABLE.

Qualify * - Qualifies all column names with table name for example if your column name X and your table name Y, then your column name becomes Y.X.  This is done for all the columns, since we had given *.

unqualify CANCL_AMT_KEY - The column CANCL_AMT_KEY is not qualified.

Qualify and Unqualify is used to avoid Synthetic problems.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     Qualify Appends the Table name before the field name;

     Qualify *; is to add table name to all fields in the tables that are going to load after that statement.

     UnQualify CANCL_AMT_KEY; is to unqualify the Field in the name CANCL_AMT_KEY;

Here you have all fields common between both tables.but need to join on

[Recorded year month]&[Recorded year month]&[Sales order] fields only so you created a key by  concatenating this field data.

Qlikview Joins automatically based on the common field name so only There is a unqualified it.

Celambarasan

[#4]

Hey jagan,

On running this code i get a message of table B not found and Result table not found when reloading

Could you possibly explain joining tables using an example data?

Thanking in advance!

[jagan]

Hi,

Check this script

qualify *;

unqualify CANCL_AMT_KEY;

A:

Directory;

LOAD

     SrNo,

     [Recorded year month]&[Recorded year month]&[Sales order] as CANCL_AMT_KEY,

     Product,

     [Sales order],

     [Adjusted rental amount based on account receivable amount],

     [Cancellation flag]

;//replacewith SQL start      Directory;

       LOAD * FROM testdata.xls

       (biff, embedded labels, table is CANCL_AMT$);//replacewith SQL end

B:

LOAD

     SrNo,

     [Recorded year month]&[Recorded year month]&[Sales order] as CANCL_AMT_KEY,

     [Recorded year month]+1 as [Recorded year month],

     Product,

     [Sales order],

     [Adjusted rental amount based on account receivable amount],

     [Cancellation flag]

;//replacewith SQL start      Directory;

LOAD * FROM testdata.xls

(biff, embedded labels, table is Temp$);//replacewith SQL endunqualify *;

Left join (A)

LOAD

    *

RESIDENT B;

DROP TABLE B;

RESULT_TABLE:LOAD

     A.SrNo,

     A.Product,

     [A.Sales order],

     [A.Adjusted rental amount based on account receivable amount] - [B.Adjustedrental amount based on account receivable amount] as [Adjusted rental amount based onaccount receivable amount],

     [A.Cancellation flag] RESIDENT A;

drop table A;

Regards,

Jagan.