topic Re: How can we do union in qlikview. in QlikView

How can we do union in qlikview.

Tue, 07 Jan 2014 04:23:42 GMT

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.

Re: How can we do union in qlikview.

tresB — Tue, 07 Jan 2014 04:34:26 GMT

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.

Re: How can we do union in qlikview.

siva0606 — Tue, 07 Jan 2014 04:42:03 GMT

Replace Concatenate instead of Union. It will work..

Regards,

Sivasu

Re: How can we do union in qlikview.

jonathandienst — Tue, 07 Jan 2014 05:27:28 GMT

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

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 08:45:05 GMT

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

Re: How can we do union in qlikview.

Anonymous — Tue, 07 Jan 2014 08:58:12 GMT

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

Re: How can we do union in qlikview.

sanketkhunte — Tue, 07 Jan 2014 08:59:42 GMT

Perfect Solution.

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 09:03:32 GMT

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

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 09:04:50 GMT

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

Thanks

Shiven..

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 09:10:01 GMT

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

Thanks,

Shiven..

Re: How can we do union in qlikview.

Anonymous — Tue, 07 Jan 2014 09:20:18 GMT

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

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

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 09:20:20 GMT

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

Re: Re: How can we do union in qlikview.

Anonymous — Tue, 07 Jan 2014 09:30:06 GMT

PFA,

NO concatenate is bit differenrt as you told...

Re: How can we do union in qlikview.

Colin-Albert — Tue, 07 Jan 2014 09:50:57 GMT

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 ;

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 10:16:38 GMT

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

Re: How can we do union in qlikview.

Anonymous — Tue, 07 Jan 2014 11:32:15 GMT

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

Re: How can we do union in qlikview.

Tue, 07 Jan 2014 12:45:56 GMT

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