topic Re: Help with preceding load in QlikView

Help with preceding load

markgraham123 — Fri, 12 Jun 2015 13:29:05 GMT

I have SKU, Shipdate and Units packed in the table.

1. Find the recent 30 days from today.

2. selecting the first ship date in the retrieved 30 days.

3. Calculating diff, between today and the retrieved first ship date.

I can get these with the below preceding load script.

LOAD SKU#, [Sum of Units packed in recent 30 days],(TODAY() - MinDate30) AS [Diff. of days in 30 days time frame];

LOAD SKU#,Sum([Units Packed 30]) as [Sum of Units packed in recent 30 days], Min(FilteredDate30) AS MinDate30

GROUP BY SKU#;

LOAD SKU#, [Units Packed] AS [Units Packed 30],[Ship Date] AS FilteredDate30

RESIDENT Table1

WHERE [Ship Date] > TODAY()-31;

But i need to calculate,

velocity = ([Sum of Units packed in recent 30 days]/[Diff. of days in 30 days time frame]) and

Relative velocity % per sku with the formula, Sum(Total(Velocity))/Velocity per SKU#.

Can someone help.

I'm struck

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 13:49:23 GMT

Try it like this:

Table2:

LOAD SKU#,

[Sum of Units packed in recent 30 days],

(TODAY() - MinDate30) AS [Diff. of days in 30 days time frame],

[Sum of Units packed in recent 30 days]/(TODAY() - MinDate30) as Velocity;

LOAD SKU#,

Sum([Units Packed 30]) as [Sum of Units packed in recent 30 days],

Min(FilteredDate30) AS MinDate30

GROUP BY SKU#;

LOAD SKU#,

[Units Packed] AS [Units Packed 30],

[Ship Date] AS FilteredDate30

RESIDENT Table1

WHERE [Ship Date] > TODAY()-31;

Join (Table2)

LOAD Sum(Velocity) as [Total Velocity]

Resident Table2;

Table3:

LOAD *,

Velocity/[Total Velocity] as [Relative Velocity %]

Resident Table2;

Drop Table Table2;

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 14:06:20 GMT

Hi Expert,

But i have a question here. Do i need to specify "Group by SKU#" in Table 3 too???

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 14:07:57 GMT

I don't think so. because right now everything in Table2 is already grouped by SKU#. and Mark, I am no Expert, I am still learning just like you. So you can call me Sunny.

Best,

Sunny

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 14:13:12 GMT

sunindia‌

Your script did the magic sunny.

One last question friend.

I'm trying to do the full accumulation for [Relative Velocity %] in the script, because i want it as a dimension so as to seperate the accumulation into buckets per %.

Is "rangesum(above(sum([Relative Velocity %]),0,rowno(total )))" accurate??

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 14:14:46 GMT

When i try the expression in CHart tbale, it just hangs the qlikview

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 14:41:44 GMT

Add this after table 3

Table4:

LOAD *,

Alt(Peek('Cumulative Relative Velocity %'), 0) + [Relative Velocity %] as [Cumulative Relative Velocity %]

Resident Table3

Order By ...;

Drop Table Table3;

You will need a order by statement to make sure the accumulation occurs in the order you want.

Give it a shot and see if it works.

Best,

Sunny

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 14:59:15 GMT

Hi Sunny,

I tried it with order by Relative Velocity%. But it is not in order.

First column shows the Relative velocity, 2nd column is the one which should look like.

3rd column is the one which is loaded by the script.

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 15:00:05 GMT

Ultimately i want to seperate the SKUs with respect to the Accumulated percentage.

0-25, 26-50, and 51-100.

So, i was trying to load from the script.

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 15:06:57 GMT

Its loading the exact reverse order.

instead of 0-100, its loading 100-0

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 15:08:10 GMT

Looks like the exact opposite

Are you doing Order By [Relative Velocity%] asc or desc? By default it would be asc, so whatever it is, try the opposite and see if that works.

Best,

Sunny

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 15:17:11 GMT

Still the same ..

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 15:19:52 GMT

This isn't working for you?

Table4:

LOAD *,

Alt(Peek('Cumulative Relative Velocity %'), 0) + [Relative Velocity %] as [Cumulative Relative Velocity %]

Resident Table3

Order By [Relative Velocity%] desc;

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 15:22:54 GMT

Hi Sunny,

Velocity or Relative Velocity %, for both its taking least value as starting point and accumulating.

But here, taking highest point as starting point and accumulation needs to be done.

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 15:25:36 GMT

I understand that is what is happening, but when I tell it to order by something desc - it should take the maximum value and order it that way. I am not sure why would it still do an asc order when we are specifying it to do desc.

Re: Help with preceding load

markgraham123 — Fri, 12 Jun 2015 15:34:05 GMT

Sunny,

Its working Perfect.

You are really awesome and genius.

If time permits,It would be my pleasure to learn few concepts in preceding load from you.

Re: Help with preceding load

sunny_talwar — Fri, 12 Jun 2015 15:42:27 GMT

Awesome

I am glad it worked out for you. Just follow me (I have already followed you) and we can message each other.