Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement as follows, I have two dates
There are cases Like Borrow Qty can be less than Tag Qty, in that case Borrow date can be multiple to fulfil the Tag quantity.
I have to create a straight table with tag Date, Borrow date and duration. If Borrow qty < Tag qty then current date –tag date has to be done.in that case a new row has to be created with tag date, current date as borrow date and duration.
How can it be achieved? Please suggest.
Check this out now. PFA
Table:
LOAD * INLINE [
TagNo, Tag Quantity, Borrow Quantity, Tag Date, Borrow Date
1, 6, 4, 01/11/2015, 02/12/2015
2, 4, 4, 01/11/2015, 02/12/2015
3, 4, 3, 02/12/2014, 3/01/2015
3, 4, 1, 02/12/2014, 05/01/2015
];
Join(Table)
LOAD TagNo,
Sum([Borrow Quantity]) as BorrowSum
Resident Table
Group By TagNo;
Concatenate (Table)
LOAD TagNo,
[Tag Quantity],
[Tag Quantity] - [Borrow Quantity] as [Borrow Quantity],
[Tag Date],
Today() as [Borrow Date]
Resident Table
Where [Tag Quantity] > BorrowSum/* and IsNull([Borrow Date])*/;
NewTable:
LOAD *,
[Borrow Date] - [Tag Date] as [Duration]
Resident Table;
DROP Table Table;
Best,
S
Not sure if I understand what you trying to achieve here.
You want a straight table with the three columns? Tag Date, Borrow Date and Duration, right?
When Borrow Quantity < Tag Quantity -> Current Date - Tag Date?
When Borrow Quantity >= Tag Quantity -> then what??
Best,
S
Reena Abraham wrote:
in that case a new row has to be created with tag date, current date as borrow date and duration.
If I understand you correctly, you want to create some new rows in your data based on some conditions of existing data. If this is correct, I recommend you try to apply this logic in your script using JOINS, CONCATENATE etc.
If this is not the case maybe share a file with data to make it easy to work on.
Hi,
Thanks for Your reply..
For eg. If Tag Qty=6 borrow Qty=4 ,Tag Date=11.01.2015 ,Borrow Date=12.02.15
Then The straight table should show as Below
A new row has to be created with Borrow date as Today(). and borrow Qty as Pending Fulfillment here 6-4.
Thanks
Is this what you want? PFA
Also attaching the script for your ease.
Table:
LOAD * INLINE [
Tag Quantity, Borrow Quantity, Tag Date, Borrow Date
6, 4, 01/11/2015, 02/12/2015
4, 4, 01/11/2015, 02/12/2015
];
Concatenate (Table)
LOAD [Tag Quantity],
[Tag Quantity] - [Borrow Quantity] as [Borrow Quantity],
[Tag Date],
Today() as [Borrow Date]
Resident Table
Where [Tag Quantity] > [Borrow Quantity];
NewTable:
LOAD *,
[Borrow Date] - [Tag Date] as [Duration]
Resident Table;
DROP Table Table;
Best,
S
Hi,,
Thanks for your reply .it worked.
But now I got a New issue
TagNo, Tag Quantity, Borrow Quantity, Tag Date, Borrow Date
1, 6, 4, 01/11/2015, 02/12/2015
2, 4, 4, 01/11/2015, 02/12/2015
3, 4, 3, 02/12/2014, 3/01/2015
3, 4, 1, 02/12/2014, 05/01/2015
For TagNo. 3 Tag Quantity is satisfied,but has been done in two days. in this case the current date concept should nt be applied. when using the current script two new rows are added which is nt correct.
Check this out now. PFA
Table:
LOAD * INLINE [
TagNo, Tag Quantity, Borrow Quantity, Tag Date, Borrow Date
1, 6, 4, 01/11/2015, 02/12/2015
2, 4, 4, 01/11/2015, 02/12/2015
3, 4, 3, 02/12/2014, 3/01/2015
3, 4, 1, 02/12/2014, 05/01/2015
];
Join(Table)
LOAD TagNo,
Sum([Borrow Quantity]) as BorrowSum
Resident Table
Group By TagNo;
Concatenate (Table)
LOAD TagNo,
[Tag Quantity],
[Tag Quantity] - [Borrow Quantity] as [Borrow Quantity],
[Tag Date],
Today() as [Borrow Date]
Resident Table
Where [Tag Quantity] > BorrowSum/* and IsNull([Borrow Date])*/;
NewTable:
LOAD *,
[Borrow Date] - [Tag Date] as [Duration]
Resident Table;
DROP Table Table;
Best,
S
Thank Yu..
Your very welcome. Have a good one.
Best,
S