Need to create a new column

[vardhan1305]

Hi ,

Temp:

Month         Total

may2018         50

may2018          60

may2018         70

may2018         100

may 2019         45

may 2019         50

may 2019         75

may 2019          200

aug2018           75

aug2018           34

aug2018            20

Output:

Temp:

month         total              Current

may2018         50              100

may2018          60              100

may2018         70                100

may2018         100             100

may 2019         45              200

may 2019         50              200

may 2019         75               200

may 2019          200            200

aug2018           75                20

aug2018           34                 20

aug2018            20                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.

[vishsaggi]

Try this?

Table1:

LOAD *, RowNo() AS RowNum INLINE [

Month,   TotalVal

may2018,  50

may2018,  60

may2018,  70

may2018,  100

may2019,  45

may2019,  50

may2019,  75

may2019,  200

aug2018,  75

aug2018,  34

aug2018,  20

];

LEFT JOIN(Table1)

LOAD Month,

     LastValue(TotalVal) AS Value

Resident Pivot

Group By Month;

[qlikviewwizard]

Hi

Try this.

Temp:

LOAD *,RowNo() as Rownum INLINE [

Month,Sales

may2018,50

may2018, 60

may2018,70

may2018,100

may 2019,45

may 2019,50

may 2019,75

may 2019, 200

aug2018,  75

aug2018,  34

aug2018,   20];

left JOIN (Temp)

LOAD FirstSortedValue(Sales,-Rownum) as Current, Month,1 as Flag,FirstSortedValue(Rownum,-Rownum) as MaxRow Resident Temp Group by Month;

NoConcatenate

Data:

LOAD * Resident Temp where Flag='1';

DROP TABLE Temp;

[tamilarasu]

@ arjun rao or simply ,

Temp:
LOAD *,RowNo() as Rownum INLINE [
Month,Sales
may2018,50
may2018, 60
may2018,70
may2018,100
may 2019,45
may 2019,50
may 2019,75
may 2019, 200
aug2018,  75
aug2018,  34
aug2018,   20];

NoConcatenate
Data:
LOAD Month, FirstSortedValue(Sales,-Rownum) as Sales
Resident Temp Group by Month;

DROP Table Temp;