Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rubenmarin

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.

View solution in original post

7 Replies
swuehl
MVP
MVP

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

rubenmarin

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
Author

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

rubenmarin

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
Author

with ApplyMap to do this?

rubenmarin

No, not in ApplyMap.

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