topic Re: Preceeding load with multiple statements; in QlikView

Preceeding load with multiple statements;

markgraham123 — Wed, 10 Jun 2015 14:55:23 GMT

I have a field name "Shipdate" and in my QVD.

I have to do the following with ship date:

1. Find the recent 30 days from today. If(Today()-[Ship Date]<31,[Ship Date]) as [30 Days from today]

2. selecting the first ship date in the retrieved 30 days. LOAD min([30 Days from today]) as [First Ship date in 30 days];

3. Calculating diff, between today and the retrieved first ship date. LOAD (Today()-([First Ship date in 30 days])) as [Dates Available];

I'm implementing the above code and getting the required result, but i want the same through precedding load. Help pls.

My current script:

Table1:

LOAD

SKU#,

[Ship Date],

If((Today()-[Ship Date])<31,[Ship Date]) as [30 Days from today]

[from 1.Qvd]

Data1:

LOAD min([30 Days from today]) as [First Ship date in 30 days]

Resident Table1;

[Data1_A]:

LOAD (Today()-[First Ship date in 30 days]) as [Diff. of days in 30 days time frame]

Resident Data1;

Re: Preceeding load with multiple statements;

rubenmarin — Wed, 10 Jun 2015 15:07:14 GMT

Hi Mark, try with this:

[Data1_A]:

LOAD (Today()-[First Ship date in 30 days]) as [Diff. of days in 30 days time frame];

LOAD min([30 Days from today]) as [First Ship date in 30 days];

LOAD

SKU#,

[Ship Date],

If((Today()-[Ship Date])<31,[Ship Date]) as [30 Days from today]

[from 1.Qvd];

This will only return the [Diff. of days in 30 days time frame] field, if you want all fields you'll have an issue with [First Ship date in 30 days], as it only returns one record.

Re: Preceeding load with multiple statements;

markgraham123 — Wed, 10 Jun 2015 15:13:10 GMT

Hi Ruben,

But i need all the fields...

How can i get all fields along with the preceding load statements.

Re: Preceeding load with multiple statements;

Wed, 10 Jun 2015 15:21:08 GMT

Hi Mark,

You can use:

LOAD (TODAY() - MinDate) AS DeltaDay;

LOAD Min(FilteredDate) AS MinDate;

LOAD SKU, ShipDate AS FilteredDate

RESIDENT SHIP

WHERE ShipDate > TODAY()-31;

And I have create a sample file for you. *cheers*

Hope helps,

David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: davidshuang.com

Re: Preceeding load with multiple statements;

Wed, 10 Jun 2015 15:24:39 GMT

Hi Mark,

To use all fields:

LOAD SKU, (TODAY() - MinDate) AS DeltaDay;

LOAD SKU, Min(FilteredDate) AS MinDate

GROUP BY SKU;

LOAD SKU, ShipDate AS FilteredDate

RESIDENT SHIP

WHERE ShipDate > TODAY()-31;

File updated

Hope helps,

David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: davidshuang.com

Re: Preceeding load with multiple statements;

markgraham123 — Wed, 10 Jun 2015 15:48:19 GMT

Hi David,

That worked.

Now, if i have to pull the same data with respect to 60, 90, 180 and 360 days, i followed the same approach by creating each table for 30, 60, 90, 180, and 360 days.

with condition WHERE ShipDate > TODAY()-31; WHERE ShipDate > TODAY()-61; WHERE ShipDate > TODAY()-91;

WHERE ShipDate > TODAY()-181; WHERE ShipDate > TODAY()-361;

Re: Preceeding load with multiple statements;

Wed, 10 Jun 2015 16:06:44 GMT

Yes,

Or you can try this:

LOAD SKU, FLAG_RANGE, (TODAY() - MinDate) AS DeltaDay;

LOAD SKU, FLAG_RANGE, Min(FilteredDate) AS MinDate

GROUP BY SKU, FLAG_RANGE;

LOAD SKU, ShipDate AS FilteredDate,

    IF(TODAY()-ShipDate<31,'30',

        IF(TODAY()-ShipDate<61,'60',

            IF(TODAY()-ShipDate<91,'90',

                IF(TODAY()-ShipDate<181,'180',

                    IF(TODAY()-ShipDate<361,'360'))))) AS FLAG_RANGE

RESIDENT SHIP

WHERE ShipDate > TODAY()-361;

Hope helps,

David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: davidshuang.com

Re: Preceeding load with multiple statements;

markgraham123 — Wed, 10 Jun 2015 16:31:53 GMT

Hi David,

Thank you very much.

I was trying to sum the units packed in the same interval of 30 days..
Can you please check the accuracy of the below code as its throwing an unknown error.

LOAD SKU#, Sum,(TODAY() - MinDate30) AS DeltaDay30;

LOAD SKU#,SUM([Units Packed 30]) as Sum, 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;