topic Re: Conditional display of data on a straight table in QlikView

Conditional display of data on a straight table

Fri, 05 Oct 2012 05:34:02 GMT

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

Re: Conditional display of data on a straight table

sivarajs — Fri, 05 Oct 2012 06:08:07 GMT

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

Re: Conditional display of data on a straight table

Fri, 05 Oct 2012 06:08:09 GMT

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

Re: Conditional display of data on a straight table

Anonymous — Fri, 05 Oct 2012 06:18:36 GMT

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;

Re: Conditional display of data on a straight table

Anonymous — Fri, 05 Oct 2012 06:19:09 GMT

hi faisal

Plz find attached

Re: Conditional display of data on a straight table

Fri, 05 Oct 2012 07:24:16 GMT

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

Re: Conditional display of data on a straight table

Gysbert_Wassenaar — Sat, 06 Oct 2012 11:08:46 GMT

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;