How can we do union in qlikview.

[#1]

SELECT DISTINCT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_ofcr_cd,

       v_t3k_acct.inv_ofcr_nm,

       'inv_ofcr' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct

  UNION

  SELECT DISTINCT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_portfolio_mgr_cd,

       v_t3k_acct.inv_portfolio_mgr_desc,

        'inv_portfolio_mgr' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct

  UNION

  SELECT v_t3k_acct.acct_nbr,

         v_t3k_acct.inv_portfolio_spcl_cd,

       v_t3k_acct.inv_portfolio_spcl_desc,

       'inv_portfolio_spcl' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct

Thank you.

[Anonymous]

NOCONCATENATE is used when we forcibly define that the two tables should not concatenate.

It is of great use when using Resident loads......

[#3]

Hi Shivendra,

Concatenate will append the data from both the tables into one. NOCONCATENATE will give the output as same but internally both of them treat as two separate tables.

Example

Table1:                 Table2:

A B                         A B

1 2                          4 5

2 3                          5 6

With the concatenate the Resultant Table will be :

A B

1 2

2 3

4 5

5 6

With NONConcatenate the resultant table is

A B A B

1 2

2 3

       4 5

       5 6

[Anonymous]

PFA,

NO concatenate is bit differenrt as you told...

[Colin-Albert]

It is best to add a LOAD command preceeding the SQL SELECT to allow you to use QlikView commands in the load script.

The script below will bring your data into a single table with separate columns for the 'inv_ofcr' , 'inv_portfolio_mgr'  and 'inv_portfolio_spcl' fields.

Regards

Colin

--------------------------------------

AcctData:

LOAD

     v_t3k_acct.acct_nbr         as acct_nbr,

     v_t3k_acct.inv_ofcr_cd      as inv_ofcr_cd,

     v_t3k_acct.inv_ofcr_nm      as inv_ofcr_nm ;

SQL SELECT DISTINCT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_ofcr_cd,

       v_t3k_acct.inv_ofcr_nm,

       'inv_ofcr' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct ;

join (AcctData)

LOAD

     v_t3k_acct.acct_nbr                  as acct_nbr,

     v_t3k_acct.inv_portfolio_mgr_cd      as inv_portfolio_mgr_cd,

     v_t3k_acct.inv_portfolio_mgr_desc    as inv_portfolio_mgr_desc  ;

SQL   SELECT DISTINCT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_portfolio_mgr_cd,

       v_t3k_acct.inv_portfolio_mgr_desc,

        'inv_portfolio_mgr' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct ;

join (AcctData)

LOAD

     v_t3k_acct.acct_nbr                  as acct_nbr,

     v_t3k_acct.inv_portfolio_spcl_cd     as inv_portfolio_spcl_cd,

     v_t3k_acct.inv_portfolio_spcl_desc   as inv_portfolio_spcl_desc  ;

SQL SELECT v_t3k_acct.acct_nbr,

         v_t3k_acct.inv_portfolio_spcl_cd,

       v_t3k_acct.inv_portfolio_spcl_desc,

       'inv_portfolio_spcl' AS Officer_Type

  FROM wonderbase.dbo.v_t3k_acct v_t3k_acct ;

[#6]

Hi

The output is same for both the funtion, then what is the use of NoConcatenate? Only mapping is bit different but visual output is same.

Thanks

Shiven..

[Anonymous]

Via noconcatenate, duplicates are being handled, as in union in oracle,

but in concatenate duplicates are not handled as in unionall in oracle.

Visually its same as table box or dimension in charts handle duplicates, put rowno() in expression , you wll get the difference..

Hope it helps....

[#8]

Hi Nitin,

I have tried and got one Good difference.

If you load two tables and Concatenate both and then if you drop anyone then both table will get dropped ,

but If you use NoConcatenate both tables and try to drop anyone then only that will go.

Anyways Thanks for your Fruitful info.

Thanks,

Shiven..