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

[matthewp]

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)

[matthewp]

is there a mapping or apply map function to do this?

[sunny_talwar]

Would you be able to create a small sample (with may be 10-15 lines of data for the two tables and the expected output you are aiming for? The problem is that we are not understanding the thing that you need.

[diego_a_barboza]

I Think it would be easier making a two steps load, something like this:

// 1- Load all data of both tables

Customers_temp:

LOAD SIAA.C_ID as [Customer ID],

     SIAA.C_NAME as Customer_name1

FROM [$(vDetailsQVDDirectory)\SIAA.qvd] (qvd);

Join 

LOAD [Customer ID], [Customer name] as Customer_name2

FROM [$(vDetailsQVDDirectory)\CTA.qvd] (qvd);

// 2- Filter out the Customer Name

NoConcatenate

Customers:

LOAD [Customer ID],

     if( Customer_name1 = null(), Customer_name2, Customer_name1) as [Customer name]

RESIDENT Customers_temp;

drop table Customers_temp;

// In case it takes too long (because you are working with a lot of records) you may want to store the data in a temporal qvd file instead of using the resident table, that should do the trick.

[cjohnson]

Hi Matthew,

Pardon me if someone has already suggested this -- but could you add a flag that indicates the Source and then use the following expression to get what you need?

IF(COUNT(DISTINCT [Customer Name]) > 1, ONLY({<Source={'1'}>}[Customer Name]),ONLY([Customer Name]))

Please see attached for more details.

Thanks,

Camile

[cjohnson]

Or if you prefer to refrain from using an if statement in an expression you could try the following (pushing the calculation back to the script):

If you put this new field as part of your schema (as a dimensional table) will allow you to easily use that field as a dimension.

See attached for more details.

Thanks,

Camile

[#7]

use mapping laod and followed by apply map function

>applymap('Tab1',Cost_Name,'Null') as New_Cust_Name

hope this will help you

[matthewp]

Ok this is the closest ive been!

Customer ID's and names working as they should, i just have an issue with values,

if there in table 1 its multiple quantitys and multiple dates and if there in table 1 and 2 its multiple quantitys and multiple dates which is fine.

however its only bringing through one quantity from 1 date if the ID is only in table 2.

[sunny_talwar]

I worked on a sample for you to show how concatenate would work:

Table1:

ID	Name	Invoice Date	Bert Code
1	John	9/11/2015	ABC
2	Alan	3/23/2016	DEF
3	Brett	12/17/2015	GHI
4	Carl	3/23/2016	JKL
5	Dan	9/26/2015	MNO
6	Elvis	11/12/2015	PQR
7	Fred	11/16/2015	STU
8	Gregg	1/25/2016	SHD
9	Han	1/3/2016	WME
10	Joe	12/28/2015	DFS

Table2: 

ID	Name	Invoice Date	Bert Code
3	Brett	11/26/2015	ABC
4	Carl	10/6/2015	DEF
5	Danny	4/20/2015	SKD
6	Elvis	8/9/2015	JKL
7	Fred	12/7/2015	MNO
8	Gregg	4/5/2016	SDW
9	Hannah	1/31/2016	STU
10	Joe	7/10/2015	FEF
11	Kris	6/10/2015	WME
12	Lee	9/2/2015	ASE

Using this as input I use the following script:

Table:

LOAD ID,

    Name,

    [Invoice Date],

    [Bert Code]

FROM

DATAA.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

LOAD ID,

    Name,

    [Invoice Date],

    [Bert Code]

FROM

DATAA.xlsx

(ooxml, embedded labels, table is Sheet2)

Where not Exists(ID);

to get this output:

The logic is very simple here. If a ID exists in both table then Table2 data will be completely be ignored, but if it only exists in one of the two tables then it will be picked from that table.

I am attaching the qvw and the Excel file, please make changes to show what the issue is in your real scenario.

Best,

Sunny

[matthewp]

i dont want it to ignore table 2 data i just want it to use the name from table 1 if the ID is in  both tables

why is this so difficult to do in qlikview?? in SQL its a 2 second job

[sunny_talwar]

We are just not sure of the kind of output you are looking for. Let me try this one more time.