Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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.
Hi Ruben,
But i need all the fields...
How can i get all fields along with the preceding load statements.
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
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
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;
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
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;