topic Re: Field not connecting properly in QlikView

Field not connecting properly

Tue, 21 Jul 2015 06:50:55 GMT

I am working on an invoicing issue and cannot figure out why a field isn't connecting properly.

Each invoice has a certain number of lines and the vendor number of that vendor appears on only 1 line of that invoice. In order to get the name of the vendor I have to connect this to another table where I have the same vendor number along with the vendor name. So far so good.

For another operation I needed the vendor number (and name) not to only appear on one line, but on all invoice lines. I managed to write the script (with a left join) in such a way that I now have an original field with the vendor number on the lines where they originally appeared, and a second field where the vendor number is filled in for all lines. I also duplicated the vendor number field in the second table and gave it the same name as the field that is filled in for all lines. However, in QlikView the connection is still not made and the vendor name only appears on the lines of the original field. If I could create a new field that with vendor name that connects on all lines that would be great. Below are a few images to illustrate:

Script from my original table:

TempHeaderItemSAP:

LOAD
    BELNR & BUKRS & GJAHR as %DocKey,
    BELNR,
    BUKRS,
    BUZEI,
    DMBTR,
    GJAHR,
    KOSTL,
    KSTAR,
    LIFNR,
    MANDT,
    MWSTS,
    POSID,
    Left(POSID,6) as project_code,
    PRCTR,
    SGTXT,
    SHKZG
FROM (qvd)
WHERE GJAHR > 2010 AND MANDT = 101;

Left Join
Load
%DocKey,
LIFNR as LIFNR2
Resident TempHeaderItemSAP
Where Len(Trim(LIFNR)) >0;

Script second table:

VendorTableSAP:

LOAD LAND1,
     LIFNR,
     LIFNR2,
     NAME1
FROM (qvd);

This is what the second table QVD looks like:

But as you can see from my details section, the connections are still not made between LIFNR2 and NAME1. What I really would like is a second name field (the first NAME1 to remain with only those lines, and the second to be filled in for all lines). Is this something someone can explain to me? I am not really sure what is going on.

I am also attaching the script if that is relevant for anyone.

Re: Field not connecting properly

sasiparupudi1 — Tue, 21 Jul 2015 06:58:53 GMT

try

LOAD LAND1,
     LIFNR,
    trim(LIFNR2) as LIFNR2,,
     NAME1
FROM (qvd);

Re: Field not connecting properly

jfkinspari — Tue, 21 Jul 2015 07:02:58 GMT

Hi Espen,

In order to differentiate between when displaying Name on 1 or all lines, you'll need two Vendor dimensions.

1. Vendor dimension displays name on all lines. Key field is LIFNR2. Use current dimension

2. Vendor dimensions displays name on only 1 line. Key field should be LIFNR. Load that dimension by

LOAD LAND1 AS LAND2,
LIFNR,
NAME1 AS NAME2

Resident VendorTableSAP;

The problem is that the key between FACT and Vendordimension is currently combination of LIFNR and LIFNR2

Re: Field not connecting properly

Tue, 21 Jul 2015 07:03:52 GMT

Hi,

No that still looked the same. And what I really need is a Name2 field that is filled in for all lines.

Re: Field not connecting properly

Tue, 21 Jul 2015 07:06:07 GMT

Will check if that works now.

Re: Field not connecting properly

Tue, 21 Jul 2015 07:07:49 GMT

Hi Jens,

Where do I put that Load Statement? Do mean in my Fact table? And then I would need my VendorTableSAP to be earlier in the code than the Fact table, right?

Re: Field not connecting properly

sasiparupudi1 — Tue, 21 Jul 2015 07:10:31 GMT

use trim on the both the keys

Left Join
Load
%DocKey,
Trim(LIFNR) as LIFNR2
Resident TempHeaderItemSAP
Where Len(Trim(LIFNR)) >0;

Re: Field not connecting properly

jfkinspari — Tue, 21 Jul 2015 07:13:42 GMT

Okay, if you just needs the name on all lines, you should just make the connection from the FACT to Vendor Dimension is made using only field LIFNR2.

You can do that easily by commenting/removing the field LIFNR from the fact table.

Re: Field not connecting properly

jfkinspari — Tue, 21 Jul 2015 07:14:23 GMT

The load should be at the end after the first Vendor dimension is loaded.

Re: Field not connecting properly

Tue, 21 Jul 2015 07:33:18 GMT

Hi Jens,

That was really helpful!

I have now managed it by loading the table again, this time just including LIFNR2 and renaming NAME1 as NAME2. That way I have both LIFNR, LIFNR2, NAME1, NAME2.

LOAD LIFNR2,
NAME1 as NAME2
FROM (qvd);

Re: Field not connecting properly

jfkinspari — Tue, 21 Jul 2015 07:37:24 GMT

All right

Have a look in the table viewer to confirm, that you don't have any synthetic keys left

Re: Field not connecting properly

Tue, 21 Jul 2015 07:38:43 GMT

Everything looks good to me! 🙂

Re: Field not connecting properly

sasiparupudi1 — Tue, 21 Jul 2015 07:43:56 GMT

Please close this thread by marking the correct answer