topic Re: Linking Fields in QlikView

Linking Fields

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have data from two tables with the same name, Program ID. However, the data in one table has the prefix. "PRG-" before the program number and the other filed only has the number.

To illustrate:

Table 1 -

Program ID:

1010102

1010103

1010104

Table 2 -

Program ID:

PRG-1010102

PRG-1010103

PRG-1010104

How can I make these two join in the load statement?

Re: Linking Fields

sunny_talwar — Wed, 12 Oct 2016 20:10:02 GMT

May be like this:

LOAD [Program ID),

...

FROM Table1;

Join

LOAD [Program ID] as [Original Program ID],

SubField([Program ID], '-', 2) as [Program ID]

....

FROM Table2;

Re: Linking Fields

Wed, 12 Oct 2016 21:40:46 GMT

Sunny,

SubField([Program ID], '-',2)

Does this cut the "PRG-" from the fields to join at the numbers?

Re: Linking Fields

sunny_talwar — Wed, 12 Oct 2016 21:44:28 GMT

Yes, is it not working? May be you need to do this:

Num#(SubField([Program ID], '-',2))

SubField([Program ID], '-',2) * 1

Re: Linking Fields

Wed, 12 Oct 2016 22:02:53 GMT

Here is my scrip, where do you add the join?

Directory;
LOAD [Program ID],
Address,
SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Directory;
LOAD [Program ID],
[Program Revision],
Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

Re: Linking Fields

sunny_talwar — Wed, 12 Oct 2016 23:41:58 GMT

May be like this:

Table:
LOAD Num#(SubField([Program ID], '-',2)) as [Program ID],
Address,
SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Join (Table)
LOAD [Program ID],
[Program Revision],
Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

Re: Linking Fields

MarcoWedel — Wed, 12 Oct 2016 23:42:15 GMT

maybe also possible:

LOAD Mid([Program ID],5) as [Program ID]

...

FROM ...

hope this helps

regards

Marco

Re: Linking Fields

hemachandran — Thu, 13 Oct 2016 06:37:22 GMT

hi,

Try this expression,

Table2:
LOAD Num#(SubField([Program ID]),'-',2)) as [Program ID]
Address,
SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Join
LOAD [Program ID],
[Program Revision],
Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

Linking Fields

Re: Linking Fields

Thu, 13 Oct 2016 16:33:35 GMT

Hi Sunny,

Your first suggestion was correct and I am marking it as such. However, the article The Secret Life of SubField was very helpful in knowing exactly how to setup my script.

The Secret Life of SubField

I used this for my script:

LOAD Distinct
Subfield("Program_ID", '-') as [Program_ID],

I didn't need a join statement since the other table had the same Program ID after the '-'. My connection is as expected and returning the data properly.

Thanks!

Re: Linking Fields

Thu, 13 Oct 2016 21:22:35 GMT

Sunny,

One clarification: Subfield([Program ID], '-',2)

What is the 2 doing? And is there a way to have the same work for data before the '-'?

Re: Linking Fields

MarcoWedel — Thu, 13 Oct 2016 21:45:13 GMT

Hi,

2 only takes the second '-'delimited subfield, while leaving out this third parameter in the script loads one row for all the subfields, i.e. it creates additional rows.

So in this case you should be cautious, as additional rows might change some aggregation results in your application (e.g. sums or counts).

The three parameter SubField() version seems the better choice here as it doen't change the row count while the matching information is restricted to only the second subfield anyhow.

hope this helps

regards

Marco