Inability to create an expression just after last dimension loaded in the script

[nevilledhamsiri]

Hi

Please advise as to why we cannot write an expression just after the last dimension loaded in the script. If the expression written as per the loaded data just under class, results are not achievable. So what I do is always shift the dimension which is to be used against the expression just above the last column in the excel where data are ready for loading.

Thanks

Neville

Directory;

LOAD DATE,

     [CLAIM NUMBER],

     [AMOUNT PAID],

     IF(([AMOUNT PAID]>0)AND([AMOUNT PAID]<=25000),'0-25000',

     IF(([AMOUNT PAID]>25000)AND([AMOUNT PAID]<=50000),'25000-50000',

     IF(([AMOUNT PAID]>50000)AND([AMOUNT PAID]<=100000),'50000-100000',

     IF(([AMOUNT PAID]>100000)AND([AMOUNT PAID]<=500000),'100000-500000',

     IF(([AMOUNT PAID]>500000)AND([AMOUNT PAID]<=1000000),'500000-1000000','>1000000')))))AS GROUP,

     CLASS

FROM

[..\CLAIM.xlsx]

(ooxml, embedded labels, table is Sheet2);

[vinieme12]

Are you getting an error message?

[vinieme12]

try as below

Directory;

LOAD DATE,

     [CLAIM NUMBER],

     [AMOUNT PAID],

     IF([AMOUNT PAID]>1000000,'>1000000') ,

         IF([AMOUNT PAID]>500000,'500000-1000000',

               IF([AMOUNT PAID]>100000,'100000-500000',

                    IF([AMOUNT PAID]>50000,'50000-100000',

                         IF([AMOUNT PAID]>25000,'25000-50000','0-25000') ) ) ) ) as GROUP,

     CLASS

FROM

[..\CLAIM.xlsx]

(ooxml, embedded labels, table is Sheet2);

[vinieme12]

I see you have posted a lot of threads on the community but have closed only a few!

Can you take some time to close your open threads!!

Qlik Community Tip: Marking Replies as Correct or Helpful

[nevilledhamsiri]

No my issue is if the paid amount was under the class which means as the last item in the loading statement(last row), then is there a possibility to do this age bucket calculation?,

[swuehl]

It should not depend on the position in the LOAD statement, if the syntax is correct e.g. using commas to separate field definitions.

Could you post the exact statement that is failing and the error / issue you are seeing?

[effinty2112 · Accepted solution]

Hi Neville,

This is a side point to your question but you may consider this way to write your expression for GROUP

Pick(

Match(-1,

[AMOUNT PAID]<=25000,

[AMOUNT PAID]<=50000,

[AMOUNT PAID]<=100000,

[AMOUNT PAID]<=500000,

[AMOUNT PAID]<=1000000,

-1),

'0-25000',

'25000-50000',

'50000-100000',

'100000-500000',

'500000-1000000',

'>1000000' )AS GROUP

It may be just a matter of taste but you may find this easier to read and maintain.

Cheers

Andrew