Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have fields like cat , sales org and date field where i have Years from 2016 Jan to 2017 Dec.
And i have a field called cons where there are values of 2016 jan and 2017 dec.
Now i need to add one more field called Cons_LY where if i select jan 2017 it should show Jan 2016 data and similarly if i select 2017 feb it should show for feb 2017 data..
Can you please help me how can i do it.. Can it be done in script
Attached app for ref.
Thanks,
Bharat
Something like this
T1:
LOAD Sales_Organization,
Cat,
Calendar_Month_Code,
Calendar_Year_Code,
Consumption
FROM [T1.qvd](qvd)
Where Calendar_Year_Code>='2016' ;
LinkTable:
LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,
Calendar_Month_Code,
'CY' as Flag
Resident T1;
Concatenate (LinkTable)
LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,
AddYears(Calendar_Month_Code, -1) as Calendar_Month_Code,
'PY' as Flag
Resident T1;
May be add this in script?
Left Join(T1)
LOAD If(calenday_year_code=2016,Sum(Consumption)) as Field Resident T1 Where Year = '2017';
Hi Anil,
Thanks.. But i need to use field bec it has yearmonth combination.
And i tried your expression but getting error. Below code for ref:
T1:
LOAD Sales_Organization,
Cat,
Calendar_Month_Code,
Calendar_Year_Code,
Consumption
FROM
(qvd) where Calendar_Year_Code>='2016' ;
left join(T1)
LOAD
If(Calendar_Year_Code=2016,Sum(Consumption)) as Cons_LY
Resident T1
Where Calendar_Year_Code = '2017';
My bad,
T1:
LOAD Sales_Organization, Cat, Calendar_Month_Code, Calendar_Year_Code, Consumption
FROM
left join(T1)
LOAD Calendar_Month_Code, Calendar_Year_Code, If(Calendar_Year_Code=2016,Sum(Consumption)) as Cons_LY
Resident T1
Where Calendar_Year_Code = '2017' Group By Calendar_Month_Code, Calendar_Year_Code;
Sorry it is not working.
please find below requirement:
I have CalendarMonthCode Cons ConslY(i need to derive this field)
201601 100 0(bec no data for 201501)
201602 200 0
201701 300 100
201702 300 200
Hope i am clear and please let me know if you need anything more and CalendarMonthCode is the field i need to use.
Please let me know if you need anything more
Hi Bharat, one easy way can be load the data as LYConsumption, adding one year to the data:
Concatenate (T1)
LOAD Sales_Organization,
Cat,
Calendar_Month_Code,
Calendar_Year_Code+1 as Calendar_Year_Code,
Consumption as LYConsumption
Resident T1;
A where condition can be added to not generate data from current year (wich will be loaded as 2019 data).
Another options of acces data of different dates can be using an as-of calendar: The As-Of Table
Can you share T1.qvd?
Hi Sunny,
PFA qvd..
Please let me know if you need anything more.
Calendar year i have given >2016 because i have years from 1981 that is why i have given like that..
Thanks..
Hi Ruben,
Thanks but i need use CalendarMonthCode instead of Calendar_Year_Code because i needed in MonthLevel basis.
Something like this
T1:
LOAD Sales_Organization,
Cat,
Calendar_Month_Code,
Calendar_Year_Code,
Consumption
FROM [T1.qvd](qvd)
Where Calendar_Year_Code>='2016' ;
LinkTable:
LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,
Calendar_Month_Code,
'CY' as Flag
Resident T1;
Concatenate (LinkTable)
LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,
AddYears(Calendar_Month_Code, -1) as Calendar_Month_Code,
'PY' as Flag
Resident T1;