topic Re: script problem with cycles in QlikView

script problem with cycles

slribeiro — Tue, 27 Sep 2016 16:37:00 GMT

Greetings.

I'm having a problem with fill values in a cycle.

Imagine the following table...

Dates	Cycle number	Value
18/09/2016	2	-
19/09/2016	3	-
20/09/2016	1	-
21/09/2016	2	-
22/09/2016	3	-
23/09/2016	1	100
24/09/2016	2	200
25/09/2016	3	300

The ideia is to repeat all the values in the previous lines by cycle number, knowing that this cycle number can be 3 sometimes or 10 other times. The number of the cycle is not importante.

The important is to reply all values for the equal cycle number.

In this case should be something like in 22/09/2016 and 19/09/2016 the value is 300 and so on...

If you could help me I will really appreciate.

Thank in advance

Simão Ribeiro

Re: script problem with cycles

sunny_talwar — Tue, 27 Sep 2016 16:41:34 GMT

May be using this:

Table:

LOAD Dates,

[Cycle number],

Value

FROM

[https://community.qlik.com/thread/234365]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD Dates,

[Cycle number],

If([Cycle number] = Previous([Cycle number]), Alt(Peek('Value'), Value), Value) as Value

Resident Table

Order By [Cycle number], Value;

DROP Table Table;

Re: script problem with cycles

slribeiro — Tue, 27 Sep 2016 16:57:37 GMT

This doesn't work because I can't change the order of the data set.

It has to be like this... That's why the mess

Re: script problem with cycles

sunny_talwar — Tue, 27 Sep 2016 17:00:18 GMT

How about this?

Table:

LOAD Dates,

[Cycle number],

Value

FROM

[https://community.qlik.com/thread/234365]

(html, codepage is 1252, embedded labels, table is @1);

Left Join(Table)

LOAD [Cycle number],

Max(Value) as Value1

Resident Table

Group By [Cycle number];

DROP Field Value;

RENAME Field Value1 to Value;

Re: script problem with cycles

miguelbraga — Wed, 28 Sep 2016 09:28:34 GMT

How about this solution?

Table:

LOAD Dates,

[Cycle number],

Value

FROM

[https://community.qlik.com/thread/234365]

(html, codepage is 1252, embedded labels, table is @1);

LOAD *,

if(isnull(Value) or Value = '-', 0, Value) as Value1

Resident Table;

Drop Table Table;

T1:

Mapping LOAD [Cycle number],

Max(Value1) as Value2

Resident T

Group By [Cycle number];

FinalTable:

LOAD *,

ApplyMap('T1', [Cycle number]) as Value3

Resident T;

Drop Table T;

Drop Fields Value1, Value From FinalTable;

Rename Field Value3 To Value;

Best regards,

D.A. MB