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.

[tresesco]

In qv, if you load multiple tables with all fields same names, they get concatenated (equivalent to UNION). Therefore, here you to do nothing but remove the UNION keyword and it will work.

Edit: If you need the result in distinctive way like in UNION, you might want to use DISTINCT with LOAD.

[siva0606]

Replace Concatenate instead of Union. It will work..

Regards,

Sivasu

[jonathandienst]

Hi

Use concatenate AND alias the names in the second and third load to the same name:

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

Concatenate

SELECT DISTINCT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_portfolio_mgr_cd As inv_ofcr_cd,

       v_t3k_acct.inv_portfolio_mgr_desc As inv_ofcr_nm,

      'inv_portfolio_mgr' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct

Concatenate

SELECT v_t3k_acct.acct_nbr,

       v_t3k_acct.inv_portfolio_spcl_cd As inv_ofcr_cd,

       v_t3k_acct.inv_portfolio_spcl_desc As inv_ofcr_nm,

       'inv_portfolio_spcl' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct

HTH

Jonathan

[#5]

Hi,

You can go to Script page and paste your Query and replace Union with Concatenate and mentioned " SQL " befour writting each of the sql Query.

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;


Concatenate

SQL

SELECT DISTINCT v_t3k_acct.acct_nbr,

v_t3k_acct.inv_portfolio_mgr_cd As inv_ofcr_cd,

v_t3k_acct.inv_portfolio_mgr_desc As inv_ofcr_nm,

'inv_portfolio_mgr' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct;


Concatenate

SQL

SELECT v_t3k_acct.acct_nbr,

v_t3k_acct.inv_portfolio_spcl_cd As inv_ofcr_cd,

v_t3k_acct.inv_portfolio_spcl_desc As inv_ofcr_nm,

'inv_portfolio_spcl' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct;

Thanks,

Shiven..

[Anonymous]

Comparison of  Oracle and Qlikview Joins.................

1). "UNION"  is resembled by "OUTER JOIN"

2). "UNION ALL" is resembled by "CONCATENATE"

3). Condition >

     where T1.field1=T2.field2

                                          is "INNER JOIN" in Qlikview

4). Condition >

     where T1.field1=T2.field2(+)

                                          is "LEFT JOIN" in Qlikview

5). Condition >

     where T1.field1(+)=T2.field2

                                          is "RIGHTJOIN" in Qlikview

Regards

Nitin

[sanketkhunte]

Perfect Solution.

[#8 · Accepted solution]

Keep the both the tables filed name as same. automatically it will do union. You don't need to mention a function.in qlikview CONCATENATE is Union.

Regards,

Santhosh G

[#9]

Hi,

Thanks Nitin for the info , then as per the above query then we need to do this in script page:-

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;


outer join

SQL SELECT DISTINCT v_t3k_acct.acct_nbr,

v_t3k_acct.inv_portfolio_mgr_cd As inv_ofcr_cd,

v_t3k_acct.inv_portfolio_mgr_desc As inv_ofcr_nm,

'inv_portfolio_mgr' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct;


outer join

SQL

SELECT v_t3k_acct.acct_nbr,

v_t3k_acct.inv_portfolio_spcl_cd As inv_ofcr_cd,

v_t3k_acct.inv_portfolio_spcl_desc As inv_ofcr_nm,

'inv_portfolio_spcl' AS Officer_Type

FROM wonderbase.dbo.v_t3k_acct v_t3k_acct;

Thanks

Shiven..

[#10]

Hi

There is one more funtion " NOCONCATENATE" , then what is the diffrence between both ?

Thanks,

Shiven..