topic Re: Combine tables without common entry in QlikView

Combine tables without common entry

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have two table :

Table1: Holds all employee details without 0 planning hours(<>0)

Table2: Hold all employees with zero planning hours.(=0)

Fileds name-Employee name,Project code,week,planning hours.

So there are chances that employees in table 1 may be in table 2 for some week.

Hence, I wont a table which holds data from table1 and table 2 but make sure

In case there are same employee in table1 and table2 , we should keep the one in table 1(<>0).

Kindly assist.

Thanks,

Prajna

Re: Combine tables without common entry

muthukumar77 — Tue, 14 Mar 2017 09:12:15 GMT

Hi,

Can you send expected output?

by Sample Data

Re: Combine tables without common entry

Tue, 14 Mar 2017 09:16:06 GMT

Table1:

Prajna,P1,W1,8

Prajna,P2,W1,3

Table2:

Prajna,P3,W1,0

Ramya,P1,w1,0

Output:

Prajna,P1,W1,8

Prajna,P2,W1,3

Ramya,P1,W1,0

Thanks,

Prajna

Re: Combine tables without common entry

jonathandienst — Tue, 14 Mar 2017 09:16:39 GMT

Like this:

LOAD [Employee name],[Project code],week,[planning hours]

FROM Table1;

Concatenate

LOAD [Employee name],[Project code],week,[planning hours]

FROM Table2

Where not(Exists([Employee name]);

Re: Combine tables without common entry

PrashantSangle — Tue, 14 Mar 2017 09:19:06 GMT

Hi,

As Jonathan suggest you can use concatenate but if you have common entry in both table and you want only single entry of it then use join i.e. outer join

Regards,

Re: Combine tables without common entry

muthukumar77 — Tue, 14 Mar 2017 09:25:13 GMT

Table:

LOAD [Employee name],[Project code],week,[planning hours]

FROM Table1;

Concatenate(T1)

LOAD [Employee name],[Project code],week,[planning hours]

FROM Table2 Where not(Exists([Employee name]);

Re: Combine tables without common entry

Tue, 14 Mar 2017 09:29:58 GMT

Hi Prajna,

[Employee name] may not be enough in you case so if you need you can create key field and use it in Exists function:

LOAD Employee,

Project,

Week,

[Planning hours],

Employee&Project&Week as %Key

FROM Table1;

LOAD Employee,

Project,

Week,

[Planning hours],

Employee&Project&Week as %Key

FROM Table2

Where Not Exists (%Key,Employee&Project&Week);

BR,

Milosz

Re: Combine tables without common entry

Tue, 14 Mar 2017 09:31:03 GMT

I added you script but where not exists doesnt seem to highlight

Re: Combine tables without common entry

PrashantSangle — Tue, 14 Mar 2017 09:38:52 GMT

Hi,

try Milosz script it will work

In Jonathan script there are bracket missing if you correct it then it also work for you

Regards

Re: Combine tables without common entry

Peter_Cammaert — Tue, 14 Mar 2017 09:45:15 GMT

Relove the parenthesis between not and Exists

Re: Combine tables without common entry

Tue, 14 Mar 2017 10:16:42 GMT

Sorry some syntax problem..i corrected.Its coming.

But does it give employees from table 1 alone,I need employees in table 2 as well .

in case they are not present in table1.

As per example Ramya.I am not getting employees with 0 hours.

Regards,

Prajna