How can we access some particular value from a calculated dimension using loop?

[#1]

Hello Everyone,

Need your Help.

How can we access some particular value from a calculated dimension using loop?

Like I have a TableA

Field1     Field2     Field3

A               01-10-2013          2000

B               02-03-2013          1000

C               03-10-2013          4000

A               01-11-2013          (2000 comes from above date value)+some other field

B               03-04-2013          (1000 picked same as above)+some other field

C               03-11-2013          (4000 same as above)+some other Field

where my field 3 is calculated column.
Problem is that i have initial dates value present in this column as the next date come it access its previous given or calculated value to calculate next one.

This synario goes on,

Can we use looping or conditional access for this purpose.

Or do we have another method to pick this value.

Thanks and Regards.

[#2]

Hi,

You do not need loop.

You can use the peek function

Peek('FieldName', -Nr of line above [,Table Name])

-1 will mean the previous or the last line of the table

Fabrice

[#3]

Hi Aunez,

Thanks for reply,

But if i want to use this peek function according to the condition defined on field1 and field2

like Peek('Field3',-1,[TableName]) if the date is Previous 1 and for same field1 value.

how can i put it.

[#4]

Is there any other method to access these values as my Peek('Fieldname',-1,Table) is not showing me any result,

and i have a lot of entries , here you said that use peek function and it is very complex to declare the variables for each and every entry

[#5]

Hi,

Yes you can use the peek() function into any other function like if() or alt() :

if(Field1 = peek('Field1', -1), Field3) as Sth

alt(Number2, peek('Number', -1) ) as Number [....]

You can also use another functions inside the peek() itself like recno(), NoofRows() to fetch the appropriate line. It depends on the need.

If you need to test a lot of fields, it can become complicated to test everything, as always. Perhaps, a way would be to create new flag field that can be used later on and dropped at the end of the script.

Fabrice

[jagan]

Hi,

Try like this

Field1     Field2     Field3

A               01-10-2013          2000

B               02-03-2013          1000

C               03-10-2013          4000

A               01-11-2013          2000

B               03-04-2013          1000

C               03-11-2013          4000

LOAD

     Field1,

     Field2,

     Field3,

     If(Previous(Field1) = Field1, Previous(Field3), Field3) + Some other field

FROM DataSource

ORDER BY Field1, Field2;

Hope this helps you.

Regards,

Jagan.

[#7]

Hi Jagan,

Actually the Field3 has only first 3 values in above table, after that we need to access the above given value next time.

so the table is like:

Field1     Field2     Field3

A               01-10-2013          2000

B               02-03-2013          1000

C               03-10-2013          4000

A               01-11-2013          this will be calculated while reporting

B               03-04-2013          this will be calculated while reporting

C               03-11-2013          this will be calculated while reporting

Please help.

Thanks

[jagan]

Hi,

It is difficult to achieve this in expression, it should be done in scripting only.  Can you come with an example, what you want to do the calculation in reporting and attached some sample dashboard with explanation.  Then it would be clear to give solution to you.

Regards,

Jagan.

[#9]

Hi Jagan,

I am attaching a file in which i used your solution but my requirement is somewhat different,

here in this file i have four field

FieldA FieldB FieldC FieldD

each having some values regarding fieldA,

Load  * Inline [

FieldA,FieldB,FieldC,FieldD

A,  01-01-2013, 200, 100

B,  01-01-2013, 100,  300

C,  01-01-2013, 300,  400

A,  02-02-2013,  0,    500

B,  02-02-2013,  0,    600

C,  02-02-2013,  0,    1000

A,  10-03-2013,  0,    500

B,  10-03-2013,  0,    600

C,  10-03-2013,  0,    1000

];

Now my task is to create a new fieldE whose calculation will be done by using as follows:

FieldE=FieldC+FieldD if FieldC<>0 for each fieldA

as it get 0 in FieldC it will take previous calculated fieldE in FieldC and then perform the calculation for corresponding FieldE

as  if FieldC=0 then FieldC=Previous(FieldE) and FieldE=FieldC+FieldD.

1 example is:

FieldA	FieldB	FieldC	FieldD	FieldE
A	01-01-2013	200	100	300
A	02-02-2013	300	500	800
A	10-03-2013	800	500	1300

[jagan · Accepted solution]

Hi,

Please try this script.

Temp:

Load
FieldA,
Date(Date#(FieldB, 'DD-MM-YYYY')) aS FieldB,
FieldC,
FieldD
Inline [
FieldA,FieldB,FieldC,FieldD
A, 01-01-2013, 200, 100
B, 01-01-2013, 100,300
C, 01-01-2013, 300,400
A, 02-02-2013, 0,500
B, 02-02-2013, 0,600
C, 02-02-2013, 0,1000
A, 10-03-2013, 0,500
B, 10-03-2013, 0,600
C, 10-03-2013, 0,1000
];

Test:
LOAD
FieldA,
FieldB,
If(FieldC = 0, Peek('FieldE'), FieldC) AS FieldC,
FieldD,
If(FieldC = 0, Peek('FieldE'), FieldC) + FieldD AS FieldE
RESIDENT Temp
ORDER By FieldA, FieldB;

DROP Table Temp;

If it helps you please close this discussion by giving Correct Answer to my post.

Regards,

Jagan.