joins issue (conditional?)

Mon, 02 May 2016 12:42:29 GMT

Hello to all,

I'm facing some issues to join some tables together.

I have the following tables:

[Main Data]:
Customer Code	Customer Description
1234	xxxxxx
12345	yyyyyy
12346	zzzzz
12347	wwww
12348	ppppp

[BudgetData]:
Customer Code	Customer Description
zwert	qqqqqq
12345	yyyyyy
ishdjoakd	lllllllllllll
12347	wwaww
gsdygsau	papapap

I need to concatenate those, in order to have both information in my customer information, but considering the following condition:

- if I have the same customer code in both tables, I need the information for [Customer Description] to be loaded from [Main Data].

I tried to do this:

- Concatenate both information for [Customer Code].

- Use left join for both datasets.

Sample Script:

NoConcatenate

Customer_CONCAT:

LOAD

DISTINCT

NUM(REPLACE(REPLACE([Customer Code],'CP',''),'CROD','')) as [CorpCustomerCode]

Resident [Main Data];

Concatenate

Customer_CONCAT2:

LOAD

DISTINCT

[Customer Code] as [CorpCustomerCode]

FROM

$(vFOLDER)

(qvd);

DROP TABLE Customer_CONCAT2;

//exit script;

LEFT JOIN [Customer_CONCAT]:

CustomerData_TEMP:

LOAD

DISTINCT

NUM(REPLACE(REPLACE([Customer Code],'CP',''),'CROD','')) as [CorpCustomerCode],

[Customer] as CustomerDescription,

NUM(REPLACE([Corp/Non Corp Customer Code],'CP','')) as CCode,

[Corp/Non Corp Customer] as CCDescription,

[Customer Type Description] as CTDescription

Resident [Main Data];

LEFT JOIN [Customer_CONCAT]:

CustomerDataBudget:

LOAD

// [Customer Group] as CustomerGroup, //MA: We do not really need this information.

[Customer Code] as [CorpCustomerCode],

[Customer Name] as CustomerDescription,

[Corp/Non Corp Code] as CCode,

[Corp/Non Corp Name] as CCDescription,

'External' as CTDescription

FROM

$(vFOLDER)

(qvd);

//exit script;

DROP FIELDs [Corp/Non Corp Customer],[Corp/Non Corp Customer Code],[Customer Type Description],[Customer Code],[Customer];

RENAME Fields CorpCustomerCode TO [Customer Code],CCDescription TO [Corp/Non Corp Customer] ,CTDescription TO [Customer Type Description],CCode TO [Corp/Non Corp Customer Code], CustomerDescription TO [Customer];

NoConcatenate

CustomerData:

LOAD

Resident Customer_CONCAT;

DROP TABLE Customer_CONCAT;

exit script;

End of sample script.

The result of this script is that I 'lost' my [Customer Code] that only exist in my [BudgetData].

Any ideas on how to solve this issue?

I thought something like:

Load both datasets for [Customer Code], then concatenate those datasets. After that, include a left join with [Main Data] then for the 'missing' information (the information that I have only in [BudgetData], I would make a left join). I tried to use the 'where' statement, but it didn't worked.

Thanks for the support in advance.

Let me know if you need more information.

Kind regards,

Marco Arruda

Re: joins issue (conditional?)

rubenmarin — Mon, 02 May 2016 13:05:13 GMT

Hi Marco, if it helps, you can check previously laoded customers using Exists() function:

[Main Data]:

LOAD [Customer Code],

[Customer Description]

...

FROM...

//[BudgetData]:

Concatenate

LOAD [Customer Code],

[Customer Description]

FROM... Where Not Exists([Customer Code])

Then you have a table without duplicates from budget and you can join the remaining data.

Re: joins issue (conditional?)

Mon, 02 May 2016 14:46:08 GMT

Thanks, I'll try this way to see how it goes.

topic joins issue (conditional?) in QlikView

joins issue (conditional?)

Re: joins issue (conditional?)

Re: joins issue (conditional?)