topic Re: Inability to create an expression just after last dimension loaded in the script in QlikView

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

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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);

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

vinieme12 — Sat, 26 Aug 2017 07:38:26 GMT

Are you getting an error message?

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

vinieme12 — Sat, 26 Aug 2017 07:47:23 GMT

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);

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

vinieme12 — Sat, 26 Aug 2017 11:58:43 GMT

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

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

nevilledhamsiri — Sat, 26 Aug 2017 17:15:13 GMT

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?,

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

swuehl — Sat, 26 Aug 2017 17:33:03 GMT

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?

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

effinty2112 — Mon, 28 Aug 2017 14:24:21 GMT

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