Need to create a column

[vardhan1305]

Temp:

month         total              ROP               Current

may2018         50              May19                   200

may2018          60              May 19                  200

may2018         70               May19                    200

may2018         100              May19                   200

may 2019         45              May19                      200

may 2019         50              Aug18                    20

may 2019         75               Aug18                    20

may 2019          200            Aug18                     20

aug2018           75                Aug18                   20

aug2018           34                 Aug18                     20

aug2018            20                Aug18                     20

Above is the table 'Temp' , I need to create a new column 'Current' in it where it should display the last total value of every month  with the respective year.

Can you help me out. Thanks.

[jyothish8807]

Hi Harsha,

Please refer the attached.

Br,

KC

[jyothish8807]

[sunilkumarqv]

please  create new logical table after script

LOAD month,lastvalue(total) as Current Resident temp Group by month;

[sunny_talwar]

Another option

Table:

LOAD Date(Date#(month, 'MMMYYYY'), 'MMMYYYY') as month,

total,

Date(Date#(ROP, 'MMMYY'), 'MMMYYYY') as ROP;

LOAD * INLINE [

    month, total, ROP

    may2018, 50, May19

    may2018, 60, May19

    may2018, 70, May19

    may2018, 100, May19

    may2019, 45, May19

    may2019, 50, Aug18

    may2019, 75, Aug18

    may2019, 200, Aug18

    aug2018, 75, Aug18

    aug2018, 34, Aug18

    aug2018, 20, Aug18

];

Left Join (Table)

LOAD month as ROP,

LastValue(total) as Current

Resident Table

Group By month;