Generating one Registry over Multiple Rows

[microwin88x]

Hello,

I have the following table:

ACCT_ID	EXEC_DATE	CODE_DESC
001	05/15/2014	10 - DESC A
001	05/15/2014	20 - DESC B
001	05/15/2014	30 - DESC C
001	08/20/2014	15 - DESC D
002	10/25/2014	30 - DESC C

What I need is to load that table in order to get one registry per ACCT_ID and EXEC_DATE only for the codes starting in 10, 15 and 30. The goal is to evaluate for a specific account and date if it has the mentioned codes.

I would need to do the following in the script. This is the final table I should get in the end:

ACCT_ID	EXEC_DATE	CODE_10	CODE_15	CODE_30
001	05/15/2014	1	0	1
001	08/20/2014	0	1	0
002	10/25/2014	0	0	1

Is there any way to do this?

Thank you!

[jagan]

Hi,

Try like this

If you want in script

LOAD

*,

If(Left(CODE_DESC, 2) = '10', 1, 0) AS CODE_10,

If(Left(CODE_DESC, 2) = '15', 1, 0) AS CODE_15,

If(Left(CODE_DESC, 2) = '30', 1, 0) AS CODE_30,

FROM TableName;

Use Pivot Table:

Dimension: ACCT_ID, EXEC_DATE

Expression: Sum(CODE_10)

Sum(CODE_15)

Sum(CODE_30)

If you want this in front end

Use Pivot Table:

Dimension: ACCT_ID, EXEC_DATE, CODE_DESC

Expression:

     Count({<CODE_DESC={'10*', '15*', '30*'}>}CODE_DESC)

Now drag CODE_DESC column to top of the table to pivot.

Hope this helps you.

Regards,

Jagan.