topic Re: How can we access some particular value from a calculated dimension using loop? in QlikView

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

Tue, 12 Nov 2013 05:09:20 GMT

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.

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

Tue, 12 Nov 2013 05:48:26 GMT

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

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

Tue, 12 Nov 2013 05:58:54 GMT

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.

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

Tue, 12 Nov 2013 07:04:40 GMT

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

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

Tue, 12 Nov 2013 07:36:07 GMT

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

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

jagan — Tue, 12 Nov 2013 08:14:44 GMT

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.

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

Tue, 12 Nov 2013 09:47:54 GMT

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

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

jagan — Wed, 13 Nov 2013 03:17:16 GMT

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.

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

Wed, 13 Nov 2013 06:16:56 GMT

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

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

jagan — Wed, 13 Nov 2013 06:51:24 GMT

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.

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

Wed, 13 Nov 2013 07:59:51 GMT

Thanks Jagan,