topic Re: add a calculated row in table in QlikView

add a calculated row in table

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

I have a table that has the column "Aging" . The aging columns is any either blank, or a number from 1-1000. I want to create a new calculated column which value will be calculated using the following break down.

Aging = Blank ---> "Current"

Aging= 1- 30 ---> "1- 30 Days Late"

Aging= 31-60 ---> "31-60 Days Late"

Aging= 61-90 ---> "61-90 Days Late"

Aging= 91+ ---> "91+ Days Late"

Thank You!!!

Re: add a calculated row in table

krishnacbe — Mon, 20 Jun 2016 17:58:53 GMT

Hi,

You can use below expression in Calculated Dimension

=if(isnull(Aging),'Current',
if(Aging>=1 or Aging <=30,'1 - 30 Days Late',
if(Aging>30 or Aging <=60,'31 - 60 Days Late',
if(Aging>60 or Aging <=90,'61 - 90 Days Late',
if(Aging>90,'91+ Days Late',)))))

Regards

Krishnapriya

Re: add a calculated row in table

m_woolf — Mon, 20 Jun 2016 18:06:55 GMT

In script:

if(Aging = null() or Aging = 0,'Current,

if(Aging= >0 and Aging <=30,'1- 30 Days Late',

if(Aging= >30 and Aging <=60,'31- 60 Days Late',

if(Aging= >60 and Aging <=90,'61- 90 Days Late',

'91+ Days Late')))) as AgingCategory;

Re: add a calculated row in table

jeckstein — Mon, 20 Jun 2016 19:16:16 GMT

m w,

I am not getting the desired result from this, I am getting errors when trying to load.

should I paste this before the file path?

thanks.

Re: add a calculated row in table

jeckstein — Mon, 20 Jun 2016 19:17:08 GMT

Krishnapriya Arumugam,

I am looking for this to be directly in the script.

Is there any solution?

thanks

Re: add a calculated row in table

krishnacbe — Mon, 20 Jun 2016 20:26:14 GMT

can you give some sample data or share the qvw to troubleshoot.

Re: add a calculated row in table

sunny_talwar — Mon, 20 Jun 2016 20:27:24 GMT

What is the exact error you are getting?

Re: add a calculated row in table

tamilarasu — Tue, 21 Jun 2016 03:03:52 GMT

Hi,

You can try like below,

Load *,

If( Len(Trim(Aging))=0, 'Current',

if(Aging>0 or Aging <=30,'1 - 30 Days Late',

if(Aging>30 or Aging <=60,'31 - 60 Days Late',

if(Aging>60 or Aging <=90,'61 - 90 Days Late',

if(Aging>90,'91+ Days Late'))))) as AgingDesc

From FilePath;

Re: add a calculated row in table

tamilarasu — Tue, 21 Jun 2016 03:04:52 GMT

Re: add a calculated row in table

susovan — Tue, 21 Jun 2016 04:41:13 GMT

Hi James,

Try this process :

Table1:

LOAD * INLINE [

Agening

100

120

500

200

300

600

];

Table2:

LOAD*,

if(Agening='-','Current',

if(Agening>=1 and Agening<30,'31 - 30 Days Late',

if(Agening>=31 and Agening<60,'31 - 60 Days Late',

if(Agening>=61 and Agening<90,'61 - 90 Days Late',

if(Agening>=91,'91+ Days Late'))))) as @_Ageninng Resident Table1