Conditional display of data on a straight table

[#1]

Hi guys,

I have quick question. I hope someone has a simple answer.

I have the following data output in a straight table where CLIENT_KEY and DATE are dimension fields and AMOUNT is an expression.

CLIENT_KEY	DATE	AMOUNT
R1015	05-Sep-12	-18,192
R1015	06-Sep-12	2,889
R1015	10-Sep-12	5,819
R1720	18-Sep-12	6,450
R1720	28-Sep-12	-70
R1720	01-Oct-12	-23
R1720	04-Oct-12	1,678

I would like to only display the latest positive AMOUNT values for a given CLIENT_KEY. So it would be chronogically all the positive values for AMOUNT till it becomes negative. The data i would like to be removed from the table is can striked through. Does anyone have any ideas as to how it can be done?

CLIENT_KEY	DATE	AMOUNT
R1015	05-Sep-12	-18,192
R1015	06-Sep-12	2,889
R1015	10-Sep-12	5,819
R1720	18-Sep-12	6,450
R1720	28-Sep-12	-70
R1720	01-Oct-12	-23
R1720	04-Oct-12	1,678

[sivarajs]

Try with this expression if(sum(AMOUNT)>0,sum(AMOUNT))

[#3]

Hi faisalza,

Yu can make use of the Dimension Limits feature.

Go to Properties--> Dimension Limits-->Limits--> Show only values that are

Select --> Values that are greater than or equal to and mention 0 since you need +ve values to be shown, selct exact amount in the next field.

Regards

Satheesh

[Anonymous]

Here's one way that seems to work:

T1:

Load

          CLIENT_KEY,

          Date(Date#(DATE,'DD-MMM-YY')) as DATE,

          AMOUNT,

          if(AMOUNT>0,1) as POS_FLAG;

LOAD * INLINE [

    CLIENT_KEY, DATE, AMOUNT

    R1015, 05-Sep-12, "-18192"

    R1015, 06-Sep-12, "2889"

    R1015, 10-Sep-12, "5819"

    R1720, 18-Sep-12, "6450"

    R1720, 28-Sep-12, -70

    R1720, 01-Oct-12, -23

    R1720, 04-Oct-12, 1678

];

T2:

Load

          CLIENT_KEY,

          DATE,

          if(Peek(CLIENT_KEY)<>CLIENT_KEY,AMOUNT,if(Peek(NEW_AMOUNT)>0 and POS_FLAG=1,AMOUNT)) as NEW_AMOUNT

resident T1

order by CLIENT_KEY, DATE desc;

Drop Table T1;

[Anonymous]

hi faisal

Plz find attached

[#6]

Thanks Johannes Sunden,

This works. I tried it with different data sets.

I was hoping to do it this after the load, because the AMOUNT values in the DB are chronologically accumulated after they are loaded,

Im assuming these values could be cumulated in the load. How would i chronologically accumulate AMOUNT values for every client in the script ?

Regards

Faisal

[Gysbert_Wassenaar · Accepted solution]

Try this for T2:

T2:

Load

          CLIENT_KEY,

          DATE,

          if(Peek(CLIENT_KEY)<>CLIENT_KEY,AMOUNT,if(Peek(NEW_AMOUNT)>0 and POS_FLAG=1,AMOUNT)) as NEW_AMOUNT,

          if(Peek(CLIENT_KEY)<>CLIENT_KEY,AMOUNT,Peek(ACCUM_AMOUNT)+AMOUNT)) as ACCUM_AMOUNT

resident T1

order by CLIENT_KEY, DATE desc;