topic Re: Select code in some values in QlikView

Select code in some values

Mon, 09 Mar 2015 11:25:42 GMT

Hello.

I want to select the code in some values like in sql ( where code in ('01','11','12').

I use the fonction Qlikview match ( where match(trim(code),01','11','12') ). But the result give informations where trim(code) is different of '01','11','12'.

Match is it the correct fonction?

Best Regard's

Re: Select code in some values

anbu1984 — Mon, 09 Mar 2015 11:29:53 GMT

Match is correct function.

What other codes do you see in the table? Can you post the script?

Re: Select code in some values

manideep78 — Mon, 09 Mar 2015 11:45:24 GMT

where match(code,01,11,12)

wildmatch(code,01,11,12)

Re: Select code in some values

PrashantSangle — Mon, 09 Mar 2015 12:00:29 GMT

Hi,

Can you post your script??

If you write same code as mention above then you are missing one ' single quote before 01.

where match(trim(code),'01','11','12')

Regards,

Re: Select code in some values

Mon, 09 Mar 2015 12:20:03 GMT

Hello.

You aske me to give the scipt.

I try to send the script under your answer but i can't had attechement.

we see in the table TABLE_TT that CODE_QUAI(QUAI as CODE_QUAI) has no values.

In the script

Where Match(Trim(QUAI),'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25')

so we must have something ('Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25') in CODE_QUAI.

look on

Select code in somme values(2)

what is wrong?

Best regard's

Re: Select code in some values

manideep78 — Mon, 09 Mar 2015 12:25:17 GMT

Where Match(QUAI,'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25')

Re: Select code in some values

Mon, 09 Mar 2015 12:26:13 GMT

Do you find the attechement on Select code in somme values(2) please Mr anbu1984?

Re: Select code in some values

anbu1984 — Mon, 09 Mar 2015 12:41:19 GMT

No. If it is not huge script, you can directly paste script in reply else select "Use advanced editor" and upload script

What are the values do you see other than mentioned in your where clause "Where Match(Trim(QUAI),'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25')" ?

Re: Select code in some values

Mon, 09 Mar 2015 12:44:06 GMT

Hello Manideep78.

Trim(QUAI) alow to remouve space before and after QUAI. I think that Match(Trim(QUAI),'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25') is correct.

Re: Select code in some values

Mon, 09 Mar 2015 12:48:35 GMT

//This is an extract of the script of fact Table (TABLE_TT)

left join (TRAFICCOTENEUR_IMP_ABJ)

LOAD QUCLEUNIK,

QUAI as CODE_QUAI,

BOL_DEB,

BOL_FIN,

date(right(ACCOSTAGE,2)&'/'&left(right(ACCOSTAGE,4),2)&'/'&left(ACCOSTAGE,4),'DD/MM/YYYY') as 'Date Accostage',

H_ACCOSTAGE as 'Heure Accostage',

date(right(APPAREILLAGE,2)&'/'&left(right(APPAREILLAGE,4),2)&'/'&left(APPAREILLAGE,4),'DD/MM/YYYY') as 'Date Appareillage',

H_APPAREILLAGE as 'Heure Appareillage',

SEJOUR as 'Sejour Quai',

SITUAT as CODE_SIT,

ESCLEUNIK as ESCLEUNIK_Quai,

MVT_ACCOSTAGE,

MVT_APPAREILLAGE

FROM $(MonChemin)QUAI.qvd (qvd)

Where Match(Trim(QUAI),'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25')

and not(isnull(Trim(QUAI)));

In the fact table TABLE_TT sometime i have CODE_QUAI with moting ( - ) or CODE_QUAI which is not in (,'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25')

Thank's

Re: Select code in some values

anbu1984 — Mon, 09 Mar 2015 12:55:14 GMT

Try just Match without second condition

Where Match(Trim(QUAI),'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25');

and not(isnull(Trim(QUAI)));

Re: Select code in some values

manideep78 — Mon, 09 Mar 2015 12:58:20 GMT

remove and not(isnull(Trim(QUAI)));

Re: Select code in some values

Mon, 09 Mar 2015 12:59:18 GMT

I agy with you. I had this (and not(isnull(Trim(QUAI)))) after, where i see that in CODE_QUAI there were CODE_QUAI which is not in (,'Q21','Q21-22','Q22','Q22-23','Q24','Q24-25','Q25'). When i delete it the result is the same.

Re: Select code in some values

anbu1984 — Mon, 09 Mar 2015 13:12:44 GMT

What is the join key to Table TRAFICCOTENEUR_IMP_ABJ?

Re: Select code in some values

Mon, 09 Mar 2015 13:24:29 GMT

is it possible to send you all the sript without make it public.

The table ESCALE in join to the table QUAI by key ESCLEUNIK. So ESCALE.ESCLEUNIK join to QUAI.ESCLEUNIK

Re: Select code in some values

anbu1984 — Mon, 09 Mar 2015 13:26:48 GMT

Just noticed you have Left join. Is CODE_QUAI join key? Then extra values whatever you see, comes from table TRAFICCOTENEUR_IMP_ABJ

Re: Select code in some values

Mon, 09 Mar 2015 13:38:13 GMT

Ok i try an other join (inner join). I make you returne.

Re: Select code in some values

Mon, 09 Mar 2015 13:45:31 GMT

Think you. The result is correct now. My misteake was in the join. I use inner join to make my fact tables and by mystake i inserte left join with tha table Quai insted of inner join.

Thank you very much!