Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Hoping, someone could help me please. I have been scratching my head on this for last 3 days.
I have this in my load script:
MonthList:
Load
Evaluate(Transaction_Month) as MonthName
Inline [
Transaction_Month
addmonths('$(start_date_fiscal)',0)
addmonths('$(start_date_fiscal)',1)
addmonths('$(start_date_fiscal)',2)
addmonths('$(start_date_fiscal)',3)
addmonths('$(start_date_fiscal)',4)
](delimiter is ';');
Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1 resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4 resident MonthList;
NoConcatenate
FinalFiscal:
Load
ID,MonthName,Quarter1,Quarter4, Coalesce(Quarter1,Quarter4) as FinalQuarter
resident Fiscal1;
drop table Fiscal1;
The output of this in table is:
Somehow, I can't able to get the correct value in FinalQuarter for ID=4.
I have also tried the IF expression in load script instead of coalesce like, but it still doesn't work
ID,MonthName,Quarter1,Quarter4, if(ID=4, Quarter4,(IF(ID=1, Quarter1))) ) as FinalQuarter
Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1 resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4 resident MonthList;
Your Fiscal table will be as below
ID | MonthName | Quarter1 | Quarter 4
1 | 1/1/2019 | Jan-Mar 19 | null
1 | 1/4/2019 | Apr-Jun 19 | null
4 | 1/1/2019 | null | Jan-Mar 18
4 | 1/4/2019 | null | Apr-Jun 19
What is the point in doing coalesce in the above table when either of the two columns will always be null??
for num(1) quarter4 will always be null
for num(4) quarter1 will always be null
you can just do
Fiscal1:
Load num(1) as ID, MonthName, quartername(MonthName,0,1) as Quarter1
,quartername(MonthName,0,1) as FinalQuarter
resident MonthList;
Concatenate
Load num(4) as ID, MonthName, quartername(MonthName,0,4) as Quarter4
,quartername(MonthName,0,4) as FinalQuarter
resident MonthList;
Hi Vineeth,
I originally tried your way only, but it doesn't work. Why I was doing different column names for each load was just another way to get the original problem solved, although couldn't solve it this way too. The problem still stays the same. Somehow, the first 3 statement loads with ID=1,2 and 3 work, but after that the year in the quarter is always incorrect for ID >=4. The statement works if I wrap the quartername formula with text function, but I don't want to do that given then my column doesn't remain a date.
text(quartername(MonthName,0,4))
One more thing, it is not abut the IF function, I guess somehow the quartername function itself is not working for me in the script. Maybe, I am not using it correctly. I am sharing the sample qvf, please have a look at the ID>=4, the quarters look incorrect to me.