topic Re: Variable in Match function? in QlikView

Variable in Match function?

qlikuser222 — Thu, 08 Feb 2018 21:54:06 GMT

Hi Everyone,

I have to use match function to filter data in the script part. However i dint want to hard code values since they change everyday. Can anyone please help me understand to do them automatically using variables?

Please find attched file where i described the problem.

Thanks

Srikanth

Re: Variable in Match function?

swuehl — Thu, 08 Feb 2018 22:04:06 GMT

It might be easier to use the Exists() function to check against your field loaded from Excel:

Test:

Load * Inline [

ID, Net

1, 200

2, 300

3, 400

4, 500 ];

//****Assuming below ID data is coming from external file and they keep changing****

Load ID as IDCheck inline [

3];

NoConcatenate

Load *

Resident Test

where Exists(IDCheck, ID); //match(ID,'1','3');

drop table Test; // instead of harcoding ID values 1 and 3 is there any way get them inside match function using varibale?? like mentioned above ID's will be maintined in external file and will be chnaging and we cant hardcode them everyday

DROP FIELD IDCheck;

Alternatively if you load the ID to check first:

Load ID inline [

3];

Test:

Load * Inline [

ID, Net

1, 200

2, 300

3, 400

4, 500 ]

WHERE Exists(ID);

Re: Variable in Match function?

swuehl — Thu, 08 Feb 2018 22:08:18 GMT

And if you want to stick to the Match() function:

Test:

Load * Inline [

ID, Net

1, 200

2, 300

3, 400

4, 500 ];

//****Assuming below ID data is coming from external file and they keep changing****

Check:

LOAD Concat( chr(39) & ID & chr(39), ',') as Check;

Load ID inline [

3];

Let vCheck = Peek('Check',0);

DROP TABLE Check;

Result:

NoConcatenate

Load *

Resident Test

where match(ID,$(vCheck));

drop table Test;

Re: Variable in Match function?

Shubham_Deshmukh — Wed, 06 Mar 2019 06:43:10 GMT

Hi @swuehl ,

I am getting that values, but want to use them in Match() in expression,

// monthCombo gives 'y','o','u'

=count(DISTINCT(if(match(status,monthCombo),sale)))

But it is not working.

Please help.

Regards.

Re: Variable in Match function?

swuehl — Wed, 06 Mar 2019 18:28:36 GMT

What about

=count(DISTINCT if( status = monthCombo,sale))

Re: Variable in Match function?

Shubham_Deshmukh — Thu, 07 Mar 2019 05:45:36 GMT

Used this one and worked fine,
=count(DISTINCT(if(WildMatch(status,'*$(monthCombo)*'),sale)))
Thanks.