topic Re: Concatenate in Qlikview (union function in sql) in QlikView

Concatenate in Qlikview (union function in sql)

SK28 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I WANT TO CONCATENATE THE TWO DATA SETS..!! (JUST LIKE UNION FUNCTION IN QLIKVIEW)

Im having two dateset say DATA_SET A, DATA_SET B, DATA_SET_X

DATA_SET_A:

LOAD S_KEY as SKEY_NEW, COLUMNa, COLUMNb

Resident JOBS_TABLE

INNER JOIN (DATA_SET_A)

LOAD S_KEY as SKEY_NEW, STATUS

RESIDENT DATA_SET_X

DATA_SET_B:

LOAD S_KEY, COLUMNa, COLUMNb

Resident JOBS_TABLE

Where not exists (S_KEY, SKEY_NEW)

NOCONCATENATE

FINAL_OUTPUT:

LOAD * RESIDENT DATA_SET_A

CONCATENATE

LOAD * RESIDENT DATA_SET_B

But the issue is data_set_B is completely getting loaded(after keeping a filter(NOT EXISTS)

I WANT TO EXCLUDE THE ROWS IN DATA_SET_A

SO MY CRITERIA IS TO EXCLUDE ROWS... but full data set 2 is also getting loaded

DATA_SET_A - - > 5 LAKH RECORD

DATA,_SETS_B - - > 17Lakh records

So i want 17-5 =12 to 13 lakh record

Can you please help me to write an best code..!!

Thanks in advance..!!

Re: Concatenate in Qlikview (union function in sql)

its_anandrjs — Sat, 09 Sep 2017 04:50:06 GMT

It seems your Where exists key field not use properly can you check this did you get any error for this Where not exists ( SKEY_NEW , S_KEY )

DATA_SET_A:

LOAD S_KEY as SKEY_NEW, COLUMNa, COLUMNb

Resident JOBS_TABLE

INNER JOIN (DATA_SET_A)

LOAD S_KEY as SKEY_NEW, STATUS

RESIDENT DATA_SET_X

DATA_SET_B:

LOAD S_KEY, COLUMNa, COLUMNb

Resident JOBS_TABLE

Where not exists ( SKEY_NEW , S_KEY )

NOCONCATENATE

FINAL_OUTPUT:

LOAD * RESIDENT DATA_SET_A

CONCATENATE

LOAD * RESIDENT DATA_SET_B

Re: Concatenate in Qlikview (union function in sql)

shiveshsingh — Sat, 09 Sep 2017 16:58:11 GMT

try with below code

Where not exists (SKEY_NEW,S_KEY)

Re: Concatenate in Qlikview (union function in sql)

vishsaggi — Sat, 09 Sep 2017 20:50:28 GMT

What is your full script can you share if possible?

May be you need NOCONCATENATE before DATA_SET_A

NOCONCATENATE

DATA_SET_A:

LOAD S_KEY as SKEY_NEW, COLUMNa, COLUMNb

Resident JOBS_TABLE

INNER JOIN (DATA_SET_A)

LOAD S_KEY as SKEY_NEW, STATUS

RESIDENT DATA_SET_X

DATA_SET_B:

LOAD S_KEY, COLUMNa, COLUMNb

Resident JOBS_TABLE

Where not exists (SKEY_NEW, S_KEY)

NOCONCATENATE

FINAL_OUTPUT:

LOAD * RESIDENT DATA_SET_A

CONCATENATE

LOAD * RESIDENT DATA_SET_B

Re: Concatenate in Qlikview (union function in sql)

jagan — Mon, 11 Sep 2017 08:09:59 GMT

Hi,

You need to adjust your script like below

Where not exists (SKEY_NEW, S_KEY)

- First parameter previously existing column you want to compare with

- Second parameter current table column you want to match

Regards,

Jagan.