[ASK] UNION in QlikView?

[#1]

Hello QV user..

i have a little problem and i've googling it but find no clue

i have two queries that need to be union

queries are :

1.

LOAD

NO_REQUEST,

NO_REQUEST AS NO_REQUEST_KAPAL,

0 AS KLAIM,

'N/A' AS NAMA_DITDIB,

0 AS PCT_SHARE_KAPAL,

'0' AS NO_BERKAS,

'1' AS TIPE,

KODE_MATA_UANG,

KODE_MATA_UANG_kapal,

KODE_KAPAL,

KODE_KAPAL_kapal,

PREMI,

ApplyMap('UDW_MAP',NO_REQUEST) AS TAHUN_BULAN

resident temp_kapal;

2.

KAPAL_2:

load distinct

'0' AS NO_REQUEST,

'0' as NO_REQUEST_KAPAL,

KLAIM,

PCT_SHARE_KAPAL,

NAMA_DITDIB,

'2' AS TIPE,

NO_BERKAS,

KODE_MATA_UANG,

KODE_MATA_UANG_KAPAL,

KODE_KAPAL,

KODE_KAPAL_kapal,

0 as PREMI,

TAHUN_CL&BULAN_CL AS TAHUN_BULAN

resident temp_kapal

data is in QVD.

when i run only 1 query, i have no proble,.

but when i run 2 queries with Concatenate function, the data is decrease.

why is it happen?

is there any function for qlikview for union?

many thx

[Gysbert_Wassenaar]

Concatentate is the qlikview equivalent of the sql union. If you concatenate tables and use the distinct keyword for one of the load statements then the distinct will be applied to result of the concatenated tables, not just to the source table. That's probably the cause of what you're seeing.

[IAMDV]

Hi,

As mentioned by Gysbert, it's filtering out the data because of DISTINCT keyword in the second load statement. I'd suggest adding a flag as a field in both the tables which can be used to identify the missing values. This will be good starting point to debug this issue. If you need further help then please post the sample QVW file along with the expected results.

Recently, I made a video tutorial on Concatenate and NoConcatenate QlikView Table Operators. You can check them for more understanding.

http://qlikshare.com/1059

Good luck!

Cheers,

DV

[#4 · Accepted solution]

Hi, Below link may help you ...

http://community.qlik.com/groups/qvscriptor-from-schema-to-script/blog/2011/11/11/understanding-join...

[vlad_komarov]

I am not sure if your statement above ("Concatentate is the qlikview equivalent of the sql union") is correct. My understanding that is the equivalent of the UNION ALL. I am searching for a simple solution for actual UNION functionality (I would need a duplicate records removed in my result table). Would you suggest a simple solution for my case?