Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a interim table where I calculate the sum of market values and accruals. I'm currently grouping by valuation date and CLASS_CODE4, which is a sector code. I want to do the same but instead of putting the code, use the description of the code. It's a one to one for all my records, and I have absolute certitude of this.
Using the code field works:
load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE) + sum(VAL_RPT.ACCRUAL_AMMOUNT) as R_PIC_00200_CALCUL,
VAL_RPT.CLASS_CODE4 as R_PIC_00200_CALCUL.CLASS_CODE4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_CODE4 ;
But using the description field gives an invalid expression:
load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE) + sum(VAL_RPT.ACCRUAL_AMMOUNT) as R_PIC_00200_CALCUL,
VAL_RPT.CLASS_DESCRIPTION4 as R_PIC_00200_CALCUL.CLASS_DESCRIPTION4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_DESCRIPTION4 ;
The field exists in my resident table if you're wondering...
The only difference between bothis that one is numeric the of text. Does that change anything?
Hi Simon, you need put all fields without agregation on Group By.
Try this.
load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE) + sum(VAL_RPT.ACCRUAL_AMMOUNT) as R_PIC_00200_CALCUL,
VAL_RPT.CLASS_DESCRIPTION4 as R_PIC_00200_CALCUL.CLASS_DESCRIPTION4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_CODE4,VAL_RPT.CLASS_DESCRIPTION4 ;
Best regards.
Tonial.
Good job!!
Can't believe I skipped that.
Thanks,
I want to burry this thread now though, haha
haha...i know what you mean....but we have all been there before. this gets me from time to time lol