straight Table

[#1]

Hi All,

I have a requirement as follows, I have two dates

1. Tag creation date :  shortage in Qty then created with shortage qty as Tag Qty.
2. Borrow Date                  :  when a Qty is borrowed in order to fulfil the shortage Quantity, that date    is Borrow Date

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.

[sunny_talwar]

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

[#3]

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.

[#4]

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

[sunny_talwar]

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

[#6]

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.

[sunny_talwar · Accepted solution]

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

[#8]

Thank Yu..

[sunny_talwar]

Your very welcome. Have a good one.

Best,

S