topic Re: Problem with join in QlikView

Problem with join

Thu, 14 Feb 2013 14:23:28 GMT

I have this weird problem with a join:

Contacts:

ContactNo	Name	CustomerNo	VendorNo
CT001	Name A	CUS0022
CT002	Name B		VEN0025
CT003	Name C	CUS0024

To join with table Entries:

Entry No	Item No	Source Type	Source No
1	I123	Vendor	VEN0025
2	I456	Customer	CUS0022

So I want to add the customer or vendor name to the entries. When I use this script it doesn't work, it just adds the contacts below the entries.

Contacts:

LOAD
    "No_" as ContactNo,
    Name,
   "Customer No_" as CustomerNo,
    "Vendor No_" as VendorNo;

SQL SELECT
    "No_",
    Name,
    "Customer No_",
    "Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";

Entries:

LOAD
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
if("Source Type" = '1', "Source No_") as CustomerNo,
if("Source Type" = '2', "Source No_") as VendorNo;

//The problem has something to do with this, if I remove the if-construction and only load the customers, the join works perfectly

SQL SELECT
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
"Source Type",
"Source No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Value Entry";

NewTable:
NoConcatenate load * Resident Entries;
join (NewTable) LOAD * Resident Contacts;
Drop table Entries;
Drop table Contacts;

Re: Problem with join

Gysbert_Wassenaar — Thu, 14 Feb 2013 14:38:33 GMT

Try loading your contacts in two batches to fill a field "Source No" so you can associate your tables properly.

Contacts:

LOAD
    "No_" as ContactNo,
    Name,
   "Customer No_" as CustomerNo,
    "Vendor No_" as VendorNo,

"Customer No_" as "Source No";

SQL SELECT
    "No_",
    Name,
    "Customer No_",
    "Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"

WHERE "Customer No_" IS NOT NULL;

LOAD
    "No_" as ContactNo,
    Name,
   "Customer No_" as CustomerNo,
    "Vendor No_" as VendorNo,

"Vendor No_" as "Source No";

SQL SELECT
    "No_",
    Name,
    "Customer No_",
    "Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"

WHERE "Vendor No_" IS NOT NULL;

SQL SELECT
    "No_",
    Name,
    "Customer No_",
    "Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";

Entries:

LOAD
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",

"Source No_" as "Source No"

SQL SELECT
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
"Source Type",
"Source No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Value Entry";

Re: Problem with join

Thu, 14 Feb 2013 15:24:49 GMT

Ty for your help. I forgot to mention that it's possible that one contact has CustomerNo aswell as VendorNo filled in. Your method doesn't give good results in that situation.

Re: Problem with join

Thu, 14 Feb 2013 15:45:06 GMT

Hi,

Change your IF statement

IF( "Source Type" = '1', "Source No_") as CustomerNo,

IF( "Source Type" = '2', "Source No_") as VendorNo;

IF( "Entry No_" = '1', "Source No_") as CustomerNo,

IF( "Entry No_" = '2', "Source No_") as VendorNo;

"Source Type" are "Vendor" or "Custumer".

NOT "1" or "2"

You need to have two Table Entries:

One for "Vendor" and other for "Custumer"

Make a join between Contacts and Entries_Vendor

After between result join with Entries_Custumer.

Ex.:

NewTable:

NoConcatenate

LOAD

ContactNo,

Name,

CustomerNo,

VendorNo

Resident Contacts;

JOIN (NewTable)

LOAD

"Entry No_",

"Item No_",

"Posting Date",

"Document No_",

CustomerNo

// VendorNo

Resident Entries

WHERE Source Type = '1';

JOIN (NewTable)

LOAD

"Entry No_",

"Item No_",

"Posting Date",

"Document No_",

// CustomerNo

VendorNo

Resident Entries

WHERE Source Type = '2';

Drop table Entries;

Drop table Contacts;