12 Replies Latest reply: Nov 5, 2014 8:49 AM by Alessandro Saccone

# How to use more than 250 value in OR clause

I have QVW,which records from QVD.

I need to put condition.below like

IF(GFCID='111' OR GFCID='22' OR GFCID='33' OR ....like that...I have 250 GFCID...

.

• ###### Re: How to use more than 250 value in OR clause

simply import all the GFCID into a table with a flag next to it, let's consider the flag as 'GFCID1'

so instead of naming the GFCID in your if condition use the flag

so it will be IF(flag = 'GFCID1') where GFCID1 will represent all the GFCIDs that you mentioned.

• ###### Re: How to use more than 250 value in OR clause

That makes sense, like a lookup table in excel.

Agree on that

• ###### Re: How to use more than 250 value in OR clause

Use wild match function

WIldmatch( Field,'val1','val2','val3'------)

I will suggest  you to use not equal to if the total values you are having is 300

then just use if( Wildmatch(Field, '','' ...all 50 which you do not need to include)<>1,Yes)

• ###### Re: How to use more than 250 value in OR clause

Something like below

MapTable:

Mapping Load *, '1' as Flag Inline

[

GFCID

111

222

333

666

777

999

];

FactTable:

Load *, ApplyMap('MapTable',GFCID,'2') as Flag Inline

[

GFCID, NUMBER

111, 100

222, 200

333, 300

444, 400

555, 500

666, 600

777, 700

888, 800

999, 900

];

use below expression

=SUM({<Flag = {2}>}NUMBER)

• ###### Re: How to use more than 250 value in OR clause

if(match(L2GFCID,'0000546429','1000733144','1004647994','1005050649','1001955051','1005291042','1011859921','1002869965','1002134833','1004284298','1001202878',

'1001517356','1001102733','1002566288','1007918336','1004236609','1015491317','1005218795','1003015587','1000555262','1002564528','1004429008',

'1000674733','1001322873','1001699047','1002022814','1003945363','0000647314','1001732125','1003577844','1002741632','1005064321','1000597534',

'1002570633','1019229145','1011782406','1003883821','1013536208','1000278641','1001123811','1002038044','1003507927','1005552652','1000952458','1000278528',

'1001534021','1008282427','1005578473','1002054848','1005921925','0000664294','1005180518','1004577643','1008482191','1005830342','1011646464','1005741633',

'1001670375','1000100036','1000486775','1006056993','1001106399','1002895117','1002254782','1006352703','1002327968','1001844802','1000242191','1007186629',

'1006652731','1001623792','1000291664','1006675871','1000774657','1001135682','1005847709','1001791547','1001192236','1000746661','1001170003','1003950561',

'1005987896','1008569505','1006581273','1003892464','1001509361','1002731181','1004460541','1003426978','0000481947','1000285893','1002388134','1005716833',

'1003438356','1006386454','1005292618','1001586412','1005440382','1003803925','1000491752','1004104567','1008107471','1005355628','1014776849','1003828138',

'1001593745','1000860731','1001937576','1003853396','1000848588','1005518624','1005390903','1009596166','1008657315','1002258753','1003637294','1001040126',

'1000792345','1005585844','1003701022','1005213181','1002569554','1000494158','1006041945','1006909589','1003109972','1011405572','1006033438','1009782059',

'1003311399','1018493817','1001589748','1018646567','1002040146','1001832812','1001942588','1000349506','0000642169','1000457333','1008190891','1000285176',

'1017985643','1001360376','1014465487','1002748602','1002339044','0000443859','1006625505','1013349955','1009075441','1007997252','1008931565','1001994987',

'1002586602','1001162108','1003818817','1009372144','1004318389','1001482676','1005087976','1000667494','1004087492','1012930352','1001108278','1006043417',

'1008940718','1000623683','1004189759','1010797701','1001895474','1001949345','1000311452','1006603412','1007557589','1005250869','1002601628','1000508825',

'1015838406','1000593954','1005200691','1000349077','1002525751','1005738462','1003216205','1015893253','1000499702','1007040586','1004270459','1003892294',

'1017257532','1009045143','1004264017','1007718183','1002135139','1011165652','1000283394','1000935111','1000497831','1000021853','1002302418','1014355908','1003429756','1002514393','1008597746','1005716779','1013540604','1009884935',

'1002582011','1000349166','1006260108','1004810135','1001191213','1015885579','1000858311','1013473567','1014389764','1006248086','1001889555','1005908988',

'1006096863','1002416723','1000284706','1000021438','1000469307','0000534803','1003098717','1002593749','1004220419','1006101123','1000483571','1001115126',

'1003415046','0000450081','0000205958','1003867168','1008061366','1011897319','1001549231','1005286715','1001170682','1003222469','1005144058','1002772651','1006653231','1012752365','1013629184','1002377884','0000669326','1011741394','1006268109','1013999976','0000562939','1006248094','1002128779','1007062458','1001158062','1003216264',

'1004484505','1003034883','1000624981','1007391788','1016609079','1008091567','1004246051') OR L4GFCID='1001116734','NAM',

if(match(L2GFCID,1000021632','1018517201','1001775754','1000349212','1000110651','1018518054','1008062923','1006506883','1006808103','1001566993','1000020156','1000021608','1000035099','1000077247','1000076542','1000220783','1000078081','1000092491','1000245018','1000092637','1000075333','1000048735','1000455993','1000082356','1000052651','1006230098','1000021705','1001329568','1000065249','1000138505','1000049707','1000193832','1000512172','1000072962','1000059621','1000097809','1002323482','1000849339','1000451378','1009228868','1000041374','1000089334','1005413636','1001705675','1000019751','1006508231','1008522819','1004513548','1001992194','1004691489','1001185388','1001345202','1000020075','1000733829','1003790211','1004692558','9999918708','1003040484','1000878266','1001632899','1002385836','1005224523','1004657698','1000107995','1001732516','1000097701','1001840343','1000291605','1000026359','1000112859','1000291958','1001890022','1003187132','1013207832','1001563463','1000020407','1000020598','1000020539','1002568817','1000766174','1001832995','1004776484','1003703971','1003195755','1010374819','1000020547','1001627208','1009248451','1004489159','1002172921','1006319609','1000482486','1002109375','1000565144','1008307152','1000510218','1002677624','1000553804','1006594014','1004086739','1007347789','1018534769','1004672638','1008040431','1012164501','1003226219','1018455087','1000166185','1000170808','1002917846','1000170476','1013445571','1002500953','1000170921','1004324818','1001611727','1000277556','1002151924','1003232103','1000523506','1000190159','1004056902','1004118274','1005805801','1002448773','1000184906','1013904886','1007220436','1004545334','1000866152','1002534734','1003140861','1010646908','1005808231','1013915306','1005595688','1010006704','1000452315','1008228864','1001262757','1000469471','1001620637','1002064207','1002638122','1003187124','1000160144','1000109343','1000253541','1001209937','1000557559','1008118295','1004646041','1000296437','1002062247','1008424906','1007237444','1006410088','1000459263','1000197331','1004280616','1000200642','1000512369','1003890267','1003311089','1000275537','1004714829','1000201509','1006722217','1000160225','1000204028','1000449829','1002566407','1001076252','1006699002','1000207272','1002050192','1003641305','1008056958','1007755755','1002566423','1000271051','1013268815','1000523921','1000023201','1004334953','1000022418','1015515089','1000606959','1000458127','1007395643','1003878011','1009001154','1010060792','1002542877','1005091361','1006056721','1007361897','1000160063','1001505463','1000022035','1000022671','1000170387','1000189002','1000109459','1004386708','1000937106','1000442131','1006600618','1003707977','1000020822','1003012952','1001182788','1009479119','1002722573','1000278676','1004937526','1000512997','1001648132','1001668419','1013930062','1006225639','1009024235','1002073192','1000265892','1002775677','1000019964','1001748307','1004023982','1018560867','1002343327','1006600154','1002547151','1002866524','1006201667'

,'1003408945','1006266114','1000020563','1003222507','1002057634','1018517473','1009832285','1004210898','1002670476','1002337661'),'EMEA',))

How to achive below one in Qlik

• ###### Re: How to use more than 250 value in OR clause

that looks like a lot of work, as Manish kachhia suggested, do a Mapping load,

maybe work your Map table in excel first, and have it load as Map.

• ###### Re: How to use more than 250 value in OR clause

just tell me any limition on this or not.
if yes..how will achive...because same thing in now working for OR clause.So I tried from Match function,but when I put another match fuction in nested IF...it is not rconozie.

if you know tell me on this.

• ###### Re: How to use more than 250 value in OR clause

no applymap and no match function, check my previous comment; load them in a table with two columns, first one is the GFCID and the second one is your flag.

• ###### Re: How to use more than 250 value in OR clause

Hi,

you can move these values in to script a separate lookup a table and have a apply map or join.

or

else add a additional flag column with these values and assign aa flag to these set of values and use in your reportng.

Regards,

Chinna

• ###### Re: How to use more than 250 value in OR clause

The best approach is to realize a table (with load * inline) containing all your value so you manage only a field and if you need to include or exclude another value you just open an excel (or txt) file.