Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Script problem

Hi,

I dont want to use "left join", so I try the script codes as follows, but it don't work well, the table NOM_PRENOM_Equal is not exist.

Some one can telle me why, thank you very much!

QUALIFY *;

REF:

LOAD [ID],

     [Nom],

     Prénom

FROM ...

;

DOU:

LOAD [ID] ,

     [Nom],

     Prénom

FROM ...

;

NOM_PRENOM_Equal:

load [REF.ID]       as [ID],

     [REF.Nom]      as [Nom],

      REF.Prénom    as [Prénom],

     [DOU.ID]       as [ID dou],

     [DOU.Nom]      as [Nom dou],

      DOU.Prénom    as [Prénom dou]

where ([REF.Nom] = [DOULON.Nom]) and (REF.Prénom = DOU.Prénom )

;

drop table DOU;

drop table REF;

1 Solution

Accepted Solutions

Re: Script problem

Hi Adami,

NOM_PRENOM_Equal will not load as that table doesn't have any source (FROM, Resident...). If you use resident it will load from one previously loaded table so you'll need to use a join or applymap.

As you're trying to load records where Nom and Prénom are equals you can try:

MAP_DOU:

Mapping LOAD Nom & '#' & Prénom, ID FROM...; //Dou table

NOM_PRENOM_Equal:

LOAD [ID],

     [Nom],

     Prénom,

     [ID dou],

     [Nom] as [Nom dou],

     Prénom as [Prénom dou]

Where not IsNull([ID dou];

LOAD [ID],

     [Nom],

     Prénom,

     ApplyMap('MAP_DOU', [Nom] & '#' & Prénom, Null()) as [ID dou]

FROM ... // Ref table

Not tested, hope this helps.

7 Replies
MVP
MVP

Re: Script problem

Your table NOM_PRENOM_Equal is missing an input table source (FROM ....)  and this can be only one table, not two.


If you don't want to join, you can look into a MAPPING or LOOKUP solution.

Re: Script problem

Do I get it right that the third LOAD statement tries to load from two tables at the same time? a LOAD statement has only one source.

Also, it seems that the third LOAD has no source specification at all... You should at least specify a FROM..., RESIDENT... INLINE, AUTOGENERATE or FROM FIELD clause. Or add another LOAD/SELECT to create a PRECEDING LOAD.

Peter

Re: Script problem

Hi Adami,

NOM_PRENOM_Equal will not load as that table doesn't have any source (FROM, Resident...). If you use resident it will load from one previously loaded table so you'll need to use a join or applymap.

As you're trying to load records where Nom and Prénom are equals you can try:

MAP_DOU:

Mapping LOAD Nom & '#' & Prénom, ID FROM...; //Dou table

NOM_PRENOM_Equal:

LOAD [ID],

     [Nom],

     Prénom,

     [ID dou],

     [Nom] as [Nom dou],

     Prénom as [Prénom dou]

Where not IsNull([ID dou];

LOAD [ID],

     [Nom],

     Prénom,

     ApplyMap('MAP_DOU', [Nom] & '#' & Prénom, Null()) as [ID dou]

FROM ... // Ref table

Not tested, hope this helps.

Not applicable

Re: Script problem

Hello, can you find  " *Nom*"  and  "*Prénom*" with this method ?

Re: Script problem

Hi Adami, you can use searches in any field of those fields, looking for "*ube*" in Nom field may filter values like 'Ruben'.

Filtering "*Nom*" in the Nom field will filter values with that pattern (*Nom*)

Not applicable

Re: Script problem

with ApplyMap to do this?

Re: Script problem

No, not in ApplyMap.

I thought you were talking about search boxes in execution time.

Community Browser