topic Re: Problem with Load Statement. in QlikView

Problem with Load Statement.

Fri, 11 Sep 2015 00:04:26 GMT

HI~ I'm Lee.

I have One Question.

for example.

Table is here

month week value check

201505 201521 10 a

201505 201522 15 a

201505 201523 12 a

201506 201521 15 a

201506 201522 13 a

201506 201523 10 a

201507 201521 20 a

201507 201522 10 a

201507 201523 30 a

201505 201521 11 b

201505 201522 12 b

201505 201523 13 b

I want to make this table in Script Edit.

month week value check FirstValue

201505 201521 10 a 10

201505 201522 15 a 10

201505 201523 12 a 10

201506 201521 15 a 15

201506 201522 13 a 15

201506 201523 10 a 15

201507 201521 20 a 20

201507 201522 10 a 20

201507 201523 30 a 20

201505 201521 11 b 11

201505 201522 12 b 11

201505 201523 13 b 11

How can I make FirstValue Column in Load Statement...

Please Help me~

Re: Problem with Load Statement.

Anonymous — Fri, 11 Sep 2015 00:31:46 GMT

Hey..Please find the app attached. Let me know if this is what you wanted. check script.

Re: Problem with Load Statement.

Fri, 11 Sep 2015 00:56:53 GMT

um.. thank you for anwser.

but all value is listed in random order.

so firstValue function is not appropriate.

is there another way?

Re: Problem with Load Statement.

Anonymous — Fri, 11 Sep 2015 01:01:38 GMT

If I correctly understand your question, I see all value in the same order as you want to see. attached is the screenshot.

Re: Problem with Load Statement.

sunny_talwar — Fri, 11 Sep 2015 03:16:46 GMT

Not sure how QlikRajan done it, but this could be another way to do it:

Table:

LOAD * Inline [

month, week, value, check, FirstValue

201505, 201521, 10, a, 10

201505, 201522, 15, a, 10

201505, 201523, 12, a, 10

201506, 201521, 15, a, 15

201506, 201522, 13, a, 15

201506, 201523, 10, a, 15

201507, 201521, 20, a, 20

201507, 201522, 10, a, 20

201507, 201523, 30, a, 20

201505, 201521, 11, b, 11

201505, 201522, 12, b, 11

201505, 201523, 13, b, 11

];

Join(Table)

LOAD month,

check,

Min(week) as week,

1 as Flag

Resident Table

Group By month, check;

FinalTable:

LOAD *,

If(Flag = 1, value, Alt(Peek('CalculatedFirstValue'), value)) as CalculatedFirstValue

Resident Table

Order By check, month, week;

DROP Table Table;

Output in a table box:

Re: Problem with Load Statement.

Fri, 11 Sep 2015 07:23:04 GMT

Thank you T.

you are right!~~~!!!

Re: Problem with Load Statement.

sunny_talwar — Fri, 11 Sep 2015 07:25:41 GMT

Awesome

I am glad we were able to help.

Best,

Sunny

Re: Problem with Load Statement.

Fri, 30 Oct 2015 07:03:01 GMT

Mr. T.

I have Other Problem.

Table:

LOAD * Inline [

month, week, value, check, FirstValue

201505, 201521, -, a, -

201505, 201522, 15, a, -

201505, 201523, 12, a, -

201506, 201521, 15, a, 15

201506, 201522, 13, a, 15

201506, 201523, 10, a, 15

201507, 201521, 20, a, 20

201507, 201522, 10, a, 20

201507, 201523, 30, a, 20

201505, 201521, 11, b, 11

201505, 201522, 12, b, 11

201505, 201523, 13, b, 11

];

if Value (First Value) is null(),

it( If(Flag = 1, value, Alt(Peek('CalculatedFirstValue'), value)) as CalculatedFirstValue) doen't Work~

How can i do...

Re: Problem with Load Statement.

sunny_talwar — Fri, 30 Oct 2015 10:44:57 GMT

Try this script in that case. Its a work around where I am giving a very high value to your null and then later assigning that same value as null.

Table:

LOAD If(len(trim(value)) > 0, value, 99999999) as value,

month,

week,

check,

If(Len(Trim(FirstValue)) > 0, FirstValue) as FirstValue

Inline [

month, week, value, check, FirstValue

201505, 201521, , a,

201505, 201522, 15, a,

201505, 201523, 12, a,

201506, 201521, 15, a, 15

201506, 201522, 13, a, 15

201506, 201523, 10, a, 15

201507, 201521, 20, a, 20

201507, 201522, 10, a, 20

201507, 201523, 30, a, 20

201505, 201521, 11, b, 11

201505, 201522, 12, b, 11

201505, 201523, 13, b, 11

];

Join(Table)

LOAD month,

check,

Min(week) as week,

1 as Flag

Resident Table

Group By month, check;

TempTable:

LOAD *,

If(Flag = 1, value, Alt(Peek('CalculatedFirstValue'), value)) as CalculatedFirstValue

Resident Table

Order By check, month, week;

FinalTable:

LOAD month,

week,

If(value <> 99999999, value) as value,

check,

FirstValue,

If(CalculatedFirstValue <> 99999999, CalculatedFirstValue) as CalculatedFirstValue

Resident TempTable;

DROP Tables Table, TempTable;

Output: