Match array

[Anonymous]

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,

[sinanozdemir]

Hi Vladimir,

This is how I did it:

Hope this helps.

Thanks

[Anonymous]

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?

[Anonymous]

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]

[sinanozdemir]

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?

[martyn_birzys]

Have a look into INTERVALMATCH()