topic Need to create a new column in QlikView

Need to create a new column

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

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.

Re: Need to create a new column

vishsaggi — Tue, 17 Jul 2018 02:43:27 GMT

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;

Re: Need to create a new column

qlikviewwizard — Tue, 17 Jul 2018 02:49:36 GMT

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;

Re: Need to create a new column

tamilarasu — Tue, 17 Jul 2018 03:37:05 GMT

@ 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;