One more field from existed field

[#1]

Hi All,

can any one help me on the below scenario,

I have a table with ID, Firm ,
   

Year, Period,Objective GLObject ,Sales, now I want a table with current year sales and Last year Sales in the same table , how to create these two fields in the same table. any help can be appreciated.

Please find the Sample data.

Regards

John

[Anonymous]

Hi John,

Maybe try:

Data:

LOAD ID,

     Firm,

     Year,

     Period,

     Objective,

     GLObject ,

     Sales

FROM ...;

MaxYear:

LOAD Max(Year) as maxYear

Resident Data;

LET vMaxYear = Peek('maxYear');

Left Join(Data)

LOAD ID,

Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

Left Join(Data)

LOAD ID,

Sales as LastYearSales

Resident Data

Where Year = ($(vMaxYear) - 1);

DROP TABLE MaxYear;

[sunny_talwar]

Looking for a front end or back end solution?

[#4]

Back End Solution

[#5]

Thanks Sunny Talwar,  I wanted to calculate an extra field in the table at back end.

[sunny_talwar]

I think solution provided by neetha P‌ should work in that case

[#7]

Thanks Neetha,

Here ID is not a unique value it has duplicate records , will this logic works ? because I have Period as filter.

Left Join(Data)

LOAD ID,

Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

[sunny_talwar]

May use add all the fields (if the combination of all the fields make a line unique)

Left Join (Data)

LOAD ID,

     Firm,

     Year,

     Period,

     Objective,

     GLObject ,

     Sales as CurrentYearSales

Resident Data

Where Year = $(vMaxYear);

[#9]

No Luck Sunny

[Anonymous]

or

create composite key for making unique value and use it.