topic Need to create a column in QlikView

Need to create a column

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

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.

Re: Need to create a column

jyothish8807 — Tue, 31 Jul 2018 06:43:46 GMT

Hi Harsha,

Please refer the attached.

Br,

Re: Need to create a column

jyothish8807 — Tue, 31 Jul 2018 06:44:37 GMT

Re: Need to create a column

sunilkumarqv — Tue, 31 Jul 2018 11:10:55 GMT

please create new logical table after script

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

Re: Need to create a column

sunny_talwar — Tue, 31 Jul 2018 11:35:23 GMT

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;