Problem with Load Statement.

[#1]

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~

[Anonymous]

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

[#3]

um.. thank you for anwser.

but all value is listed in random order.

so firstValue function is not appropriate.

is there another way?

[Anonymous]

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

[sunny_talwar · Accepted solution]

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:

[#6]

Thank you T.

you are right!~~~!!!

[sunny_talwar]

Awesome

I am glad we were able to help.

Best,

Sunny

[#8]

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...

[sunny_talwar]

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: