topic Re: straight Table in QlikView

Re: straight Table

Thu, 19 Feb 2015 12:55:58 GMT

Hi All,

I have a requirement as follows, I have two dates

Tag creation date : shortage in Qty then created with shortage qty as Tag Qty.
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.

Re: straight Table

sunny_talwar — Thu, 19 Feb 2015 13:05:11 GMT

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,

Re: straight Table

Thu, 19 Feb 2015 13:09:16 GMT

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.

Re: straight Table

Fri, 20 Feb 2015 04:05:10 GMT

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

Re: straight Table

sunny_talwar — Fri, 20 Feb 2015 04:25:25 GMT

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,

Re: straight Table

Fri, 20 Feb 2015 10:47:50 GMT

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.

Re: straight Table

sunny_talwar — Fri, 20 Feb 2015 10:59:42 GMT

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,

Re: straight Table

Fri, 20 Feb 2015 11:09:42 GMT

Thank Yu..

Re: straight Table

sunny_talwar — Fri, 20 Feb 2015 11:15:20 GMT

Your very welcome. Have a good one.

Best,