Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sibrulotte
Contributor III

Group By on a text field

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?

3 Replies
fernando_tonial
Valued Contributor

Re: Group By on a text field

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.

sibrulotte
Contributor III

Re: Group By on a text field

Good job!!

Can't believe I skipped that.

Thanks,

I want to burry this thread now though, haha

jsaradhi
Valued Contributor

Re: Group By on a text field

haha...i know what you mean....but we have all been there before. this gets me from time to time lol