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: 
qlikuser222
Creator
Creator

Variable in Match function?

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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 [

ID

1,

3];

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

DROP TABLE Check;

Result:

NoConcatenate

Load *

Resident Test

where match(ID,$(vCheck));

drop table Test;

View solution in original post

5 Replies
swuehl
MVP
MVP

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 [

ID

1,

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 [

ID

1,

3];

Test:

Load * Inline [

ID,  Net

1, 200

2, 300

3, 400

4, 500 ]

WHERE Exists(ID);

swuehl
MVP
MVP

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 [

ID

1,

3];

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

DROP TABLE Check;

Result:

NoConcatenate

Load *

Resident Test

where match(ID,$(vCheck));

drop table Test;

Shubham_Deshmukh
Specialist
Specialist

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.

swuehl
MVP
MVP

What about

 

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

 

?

Shubham_Deshmukh
Specialist
Specialist

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