topic Re: Need help how to do ? in QlikView

Need help how to do ?

subbareddykm — Mon, 28 Dec 2015 15:20:35 GMT

Hi All,

I Have one requirement like below.

Example: I ahve two tables Table _1 and Table_2.

Table_1:

Field1,

Field2,

Field3,

Field4

From................

Table_2:

Field1,

Field2

,Field3,

Field5,

Field6

From..................

Requirement is : I want to load only Where Field2=Field2 and Field3=Field3 from Table_1

Can any one tell me how to do this ,any help would be appreciated,

Thanks in advance

Re: Need help how to do ?

sunny_talwar — Mon, 28 Dec 2015 15:45:52 GMT

May be try it where exists:

Table_1:

LOAD Field1,

Field2,

Field3,

Field4

FROM....

Table2:

LOAD Field1,

Field2,

Field3,

Field4,

Field5,

Field6

FROM....

Where Exists(Field2) and Exists(Field3);

Seems like this might not work, try maxgro‌'s solution below.

Best,

Sunny

Re: Need help how to do ?

subbareddykm — Mon, 28 Dec 2015 15:55:45 GMT

But Will This same for my above logic , means in my requirement Field2=Field2 and Field3=Field3, right ?

Here we are taking entaire Field2 and Field3 from Table_1.

If we take left join Will it work ?

Re: Need help how to do ?

TKendrick20 — Mon, 28 Dec 2015 15:59:00 GMT

Sounds like you just need a left join on both Field2 and Field3 so that only data that exists for those fields in both tables will be loaded.

Re: Need help how to do ?

maxgro — Mon, 28 Dec 2015 16:01:42 GMT

or maybe this

Table_1:

Field2 & '-' & Field3 as F2F3,

Field1,

Field2,

Field3,

Field4

From................

;

Table_2:

Field1,

Field2

Field3,

Field5,

Field6

From..................

Where Exists(F2F3, Field2 & '-' & Field3)

;

Re: Need help how to do ?

TKendrick20 — Mon, 28 Dec 2015 16:02:42 GMT

I feel like I've encountered something like this before and I've needed a Qualify *; in there somewhere for this to work properly. Otherwise it might just be checking that data in Fields 2 & 3 exists.

Re: Need help how to do ?

Peter_Cammaert — Mon, 28 Dec 2015 16:07:33 GMT

Maybe a bit dangerous, this code.

Two exists() calls on different fields won't check the actual combinations of required field values, just whether those values are present somewhere in each field.

If you need rows from Table_2 that have Field2 & Field3 combinations that exist in the same record in Table_1, you need to create a Key value, or perform a LEFT JOIN.

If you just need rows in Table_1 and Table_2 that have corresponding combinations, you can use an INNER JOIN (to merge tables), or an INNER KEEP (to keep two separate tables)

Best,

Peter

PS See Massimo's post below for an example using Key values.

Re: Need help how to do ?

subbareddykm — Mon, 28 Dec 2015 16:10:10 GMT

If i use the above same code i ll get sythatic keys also right?

Re: Need help how to do ?

sunny_talwar — Mon, 28 Dec 2015 16:12:08 GMT

Thanks for pointing that out Peter. I usually like testing my solutions before proposing them, but at times when a sample isn't available I try come up with a solution which I think might work. In this case I guess my solution won't work

Re: Need help how to do ?

sunny_talwar — Mon, 28 Dec 2015 16:18:27 GMT

Yes, unless you Drop Table_1.

Re: Need help how to do ?

subbareddykm — Mon, 28 Dec 2015 16:18:55 GMT

Can i use like this , if I have more then three tables

Table_1:

LOAD Field1,

Field2,

Field3,

Field4

FROM....

Left join

Table2:

LOAD Field1,

Field2,

Field3,

Field4,

Field5,

Field6

FROM....

Left Join(Table_1)

Table_3:

Field3 ,

Field7

From.......

Re: Need help how to do ?

maxgro — Mon, 28 Dec 2015 16:59:43 GMT

The question wasn't (IMHO) very clear; for a 2 tables result I thought at my or Sunny's answer (it depends if the check was on a single row for both the fields --> my answer or in many rows --> Sunny's answer).

For a 1 table result maybe a left or inner join.

Re: Need help how to do ?

Peter_Cammaert — Mon, 28 Dec 2015 17:04:50 GMT

Of course you can.

The only thing you have to take care of is this: each successive JOIN may duplicate the records that are already present in your original table. For example, if your table_1 contains:

Field1, Field2, Field3

ABC, 2/5/2015, $1000

left joining the following to table_1

Field1, Field4

ABC, CostCenter8012

ABC, CostCenter8200

will produce this new version of Table_1:

Field1, Field2, Field3, Field4

ABC, 2/5/2015, $1000, CostCenter8012

ABC, 2/5/2015, $1000, CostCenter8200

which may not be what you expect.

The order in which you perform multiple successive JOINs is extremely important. But since you keep to the dummy field names (I haven't got any idea what they will be used for), we cannot advise on the real impact of your examples.

Best,

Peter

Re: Need help how to do ?

subbareddykm — Mon, 28 Dec 2015 17:17:50 GMT

If i dont want duplicate records, What should i do ?

Re: Need help how to do ?

Peter_Cammaert — Mon, 28 Dec 2015 17:38:46 GMT

You should think it over. You can always ask yourself: does the table I want to join to an existing table contain multiple records per distinct join field value?