topic Re: Match array in QlikView

Match array

Anonymous — Wed, 12 Aug 2015 19:17:05 GMT

Hi guys,

I have an expression in excel like

MATCH( MOD(field,364), {0,28,56,63})

Is it analog match array in QlikView?

I take a number/ on 364 then I need to check if result is between 0-28, 28-56 and so on

Thanks,

Re: Match array

sinanozdemir — Wed, 12 Aug 2015 19:33:03 GMT

Hi Vladimir,

This is how I did it:

Hope this helps.

Thanks

Re: Match array

Anonymous — Wed, 12 Aug 2015 20:47:10 GMT

I couldn't get

PICK(MATCH(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364),0,28,63,91,119,154,182,210,245,273,301,336),

'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan') as [Fiscal Month Year]

Where am I wrong?

Re: Match array

Anonymous — Wed, 12 Aug 2015 20:48:09 GMT

It works only like this

PICK(IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<28,1,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<63,2,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<91,3,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<119,4,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<154,5,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<182,6,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<210,7,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<245,8,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<273,9,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<301,10,

IF(MOD(FLOOR(Date(Date#(REPLACE(REPLACE(Data3rdInfo_Transaction_Date,'/',''),'-',''),'MMDDYYYY'),'MM/DD/YYYY'))-41672,364)<336,11,12))))))))))),

'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec','Jan') as [Fiscal Month Year]

Re: Match array

sinanozdemir — Wed, 12 Aug 2015 21:33:12 GMT

Match will be matching the exact values and in your IF statements, you are using logical operators such as '<'. I don't know what kind of value your Mod() function is returning, such as integer or float(decimals) so that could be one reason why Match() wouldn't work.

Can you post a sample of Data3rdInfo_Transaction_Date?

Re: Match array

martyn_birzys — Wed, 07 Oct 2015 16:44:16 GMT

Have a look into INTERVALMATCH()