topic Re: Script problem in QlikView

Script problem

Mon, 29 Jun 2015 14:25:24 GMT

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;

Re: Script problem

swuehl — Mon, 29 Jun 2015 14:34:52 GMT

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

Peter_Cammaert — Mon, 29 Jun 2015 14:35:41 GMT

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

rubenmarin — Mon, 29 Jun 2015 14:38:57 GMT

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.

Re: Script problem

Tue, 30 Jun 2015 08:33:21 GMT

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

Re: Script problem

rubenmarin — Tue, 30 Jun 2015 10:06:22 GMT

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*)

Re: Script problem

Tue, 30 Jun 2015 10:12:13 GMT

with ApplyMap to do this?

Re: Script problem

rubenmarin — Tue, 30 Jun 2015 10:16:21 GMT

No, not in ApplyMap.

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