topic Re: Two tables, two id's and names, only use first name in QlikView

Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 09:53:59 GMT

I have the following load in tab 1 i have

LOAD

SIAA.C_ID as [Customer ID],

SIAA.C_NAME as [Customer name]

FROM

[$(vDetailsQVDDirectory)\SIAA.qvd]

(qvd);

Then in tab 2 i have

PreIT:

Concatenate

LOAD

[Customer ID],

[Customer name]

FROM

[$(vDetailsQVDDirectory)\CTA.qvd]

(qvd);

What i want is if there is a matching Customer ID in both tabs i want it to only use the name from tab 1 (SIAA.C_NAME)

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 09:55:24 GMT

Try it with Where not Exist statement:

LOAD

SIAA.C_ID as [Customer ID],

SIAA.C_NAME as [Customer name]

FROM

[$(vDetailsQVDDirectory)\SIAA.qvd]

(qvd);

Then in tab 2 i have

PreIT:

Concatenate

LOAD

[Customer ID],

[Customer name]

FROM

[$(vDetailsQVDDirectory)\CTA.qvd]

(qvd)

Where not Exists([Customer ID]);

Re: Two tables, two id's and names, only use first name

Chanty4u — Fri, 15 Apr 2016 09:56:55 GMT

just add

last line as

where not exists([Customer ID]);

Re: Two tables, two id's and names, only use first name

Chanty4u — Fri, 15 Apr 2016 09:57:42 GMT

ohhh ur tooo fast man...

missing parenthesis in ur stmnt

Where not Exists([Customer ID]);

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 09:59:00 GMT

Thanks for pointing out. Fixed now

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:00:23 GMT

This then doesnt load any of the data from the second table

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 10:01:56 GMT

Are you sure there are Customer ID in the second load which don't match with the Customer ID from the first file?

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:06:17 GMT

i mean the other data ( i didnt list) in the second table doesnt load.

i have a graph that shows data and is split by time on the bottom.

the second table is data from 2005 - 2010 and the first tab is 2010 onward

what was happening was the graph line is splitting into two as there from two tables

i want it to be one continuous line if the ID's in both tables match.

the issue is that the name might have changed in both tables but the IDs are the same.

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 10:08:41 GMT

You will have to rename the fields to be the same when you concatenate them. I think that is the reason you are not seeing them as one line. I don't know how many fields you have, but this should be a one time effort.

Re: Two tables, two id's and names, only use first name

Kushal_Chawda — Fri, 15 Apr 2016 10:09:17 GMT

try join, instead of concatenate

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:11:51 GMT

i want to achieve this without mapping

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 10:13:00 GMT

Try Joining as mentioned by Kush may be

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:17:56 GMT

Ok this has linked them and works for the ones in both tables and uses the correct name.

HOWEVER now ones that are in table 1 but not in table 2 are missing

do i need to do a left join?

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 10:20:42 GMT

Have you cleared out all the selection? I would think that the data should be there, might be filtered out due to selections

Re: Two tables, two id's and names, only use first name

Kushal_Chawda — Fri, 15 Apr 2016 10:21:53 GMT

I think you need to perform outer join

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:36:19 GMT

Actually with a join still getting duplicate id from both table where name is different

Re: Two tables, two id's and names, only use first name

sunny_talwar — Fri, 15 Apr 2016 10:40:25 GMT

Yes because the assumption here would be that a single ID will be associated to one and only one name. If that is not true then choose whichever table has the correct names and comment out the name in the other table while joining the two tables.

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 10:42:29 GMT

tried this, just brings the same thing through except the names i dont want are all "-"

Re: Two tables, two id's and names, only use first name

Kushal_Chawda — Fri, 15 Apr 2016 10:56:31 GMT

I think Customer Name is not consistent in your table.

Data:

LOAD

SIAA.C_ID as [Customer ID],

SIAA.C_NAME as [Customer name]

1 as Flag1

FROM

[$(vDetailsQVDDirectory)\SIAA.qvd]

(qvd);

join (Data)

LOAD

[Customer ID],

[Customer name] as [Customer name1]

2 as Flag2

FROM

[$(vDetailsQVDDirectory)\CTA.qvd]

(qvd);

Final:

noconcatenate

LOAD distinct [Customer ID],

if( len(trim(Flag1))>0 or (len(trim(Flag2))>0 and len(trim(Flag1))>0), [Customer name],

[Customer name1] ) as [Customer ID]

resident Data;

drop table Data;

Re: Two tables, two id's and names, only use first name

matthewp — Fri, 15 Apr 2016 12:31:18 GMT

Field names must be unique within table

Final:

noconcatenate

LOAD distinct [Customer ID],

if( len(trim(Flag1))>0 or (len(trim(Flag2))>0 and len(trim(Flag1))>0), [Customer name],

[Customer name1] ) as [Customer ID]

resident Data