add a calculated row in table

[jeckstein]

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

[krishnacbe]

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

[m_woolf]

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;

[jeckstein]

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.

[jeckstein]

Krishnapriya Arumugam,

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

Is there any solution?

thanks

[krishnacbe]

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

[sunny_talwar]

What is the exact error you are getting?

[tamilarasu]

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;

[tamilarasu]

[susovan]

Hi James,

Try this process :

Table1:

LOAD * INLINE [

Agening

20

9

10

90

85

100

55

80

21

9

30

120

500

-

8

200

1

5

25

10

9

-

8

50

10

25

9

300

8

10

15

-

87

20

-

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