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: 
Anonymous
Not applicable

Match array

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,

5 Replies
sinanozdemir
Specialist III
Specialist III

Hi Vladimir,

This is how I did it:

Capture.PNG

Hope this helps.

Thanks

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Specialist III
Specialist III

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

Have a look into INTERVALMATCH()