topic Re: Left Join with Where statement in QlikView

Left Join with Where statement

Fri, 28 Mar 2014 12:35:39 GMT

Hi,

I have a problem.

I have two tables like this :

FindCategory:

Load * Inline
[
Code, Category
A005, Regroup1
DRAP, Regroup2

A017, Regroup1
];

MyDataBase:

Load
[Name Number] as Designation,
Art,
Text(SN) as SN,
Code
FROM

(biff, embedded labels, table is Format$);

The field named "Code" from MyDataBase contain values :

701_A005_789
966_DRAP_789

966_A005_789

...

I Want to find the Categorie from Code but inside the full code :

701_A005_789 Categorie is = Regroup1

701_DRAP_789 Categorie is = Regroup2

I've tried this but it's not working :

Left Join(FindCategory)
Load *
Resident MyDataBase

Where WildMatch(MyDataBase.Code, '*' + FindCategory.Code + '*');

Could you help me?

Re: Left Join with Where statement

jonathandienst — Fri, 28 Mar 2014 12:39:03 GMT

So what is your question?

Jonathan

Re: Left Join with Where statement

Fri, 28 Mar 2014 12:43:32 GMT

So what is your problem? Please explain.

Vinay

Re: Left Join with Where statement

Aurelien_Martinez — Fri, 28 Mar 2014 13:23:23 GMT

Hi,

MAPPING

FindCategory_MAP:

Load * Inline
[
Code, Category
A005, Regroup1
DRAP, Regroup2

A017, Regroup1
];

MyDataBase:

Load
[Name Number] as Designation,
Art,
Text(SN) as SN,
Code,

applymap('FindCategory_MAP', mid(Code, 5, 4)) as Category
FROM

(biff, embedded labels, table is Format$);

Re: Left Join with Where statement

Fri, 28 Mar 2014 13:49:57 GMT

Thank for your reply.

I understand this line : applymap('FindCategory_MAP', mid(Code, 5, 4)) as Category

but imagine that my Code is not formated like : 701_A005_789 but like 70000XXX1_A005_789?

Is it possible to use a like statement? applymap('FindCategory_MAP', *Code*) as Category

Re: Left Join with Where statement

Aurelien_Martinez — Fri, 28 Mar 2014 13:56:50 GMT

Peraps with :

applymap('FindCategory_MAP', SubField(Code, '_', 2)) as Category

Re: Left Join with Where statement

maxgro — Sat, 29 Mar 2014 12:39:03 GMT

RESULT

if you want to add the Category

using some test data for MyDataBase

SCRIPT

FindCategory:

Mapping Load * Inline

[

Code, Category

A005, Regroup1

DRAP, Regroup2

A017, Regroup1

];

MyDataBase:

Load * Inline [

Designation, Art, SN, Code

1,1,1,701_A005_789

2,2,2,701_A005_790

3,3,3,966_DRAP_789

4,4,4,966_A005_789

];

Table:

NoConcatenate load

ApplyMap('FindCategory', SubField(Code, '_', 2)) as Category

Resident

MyDataBase;

DROP Table MyDataBase;

Re: Left Join with Where statement

maxgro — Sat, 29 Mar 2014 12:46:40 GMT

if you want to load only records from MyDataBase with category in FindCategory

FindCategory:

Load * Inline

[

Code1, Category

A005, Regroup1

DRAP, Regroup2

A017, Regroup1

];

MyDataBase:

Load * Inline [

Designation, Art, SN, Code

1,1,1,701_A005_789

2,2,2,701_A005_790

3,3,3,966_DRAP_789

4,4,4,966_A005_789

5,5,5,966_A006_789

6,6,6,966_A007_789

];

Table:

NoConcatenate load

Resident

MyDataBase

Where Exists(Code1, SubField(Code, '_', 2)) ;

DROP Table MyDataBase;