# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

## Re: straight Table

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.

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: straight Table

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)

Sum([Borrow Quantity]) as BorrowSum

Resident Table

Group By TagNo;

Concatenate (Table)

[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:

[Borrow Date] - [Tag Date] as [Duration]

Resident Table;

DROP Table Table;

Best,

S

8 Replies
Highlighted
MVP

## Re: straight Table

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

Highlighted
Not applicable

## Re: straight Table

 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.

Highlighted
Not applicable

## Re: straight Table

Hi,

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

Highlighted
MVP

## Re: straight Table

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)

[Tag Quantity] - [Borrow Quantity] as [Borrow Quantity],

[Tag Date],

Today() as [Borrow Date]

Resident Table

Where [Tag Quantity] > [Borrow Quantity];

NewTable:

[Borrow Date] - [Tag Date] as [Duration]

Resident Table;

DROP Table Table;

Best,

S

Highlighted
Not applicable

## Re: straight Table

Hi,,

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.

Highlighted
MVP

## Re: straight Table

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)

Sum([Borrow Quantity]) as BorrowSum

Resident Table

Group By TagNo;

Concatenate (Table)

[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:

[Borrow Date] - [Tag Date] as [Duration]

Resident Table;

DROP Table Table;

Best,

S

Highlighted
Not applicable

Thank Yu..

Highlighted
MVP

## Re: straight Table

Your very welcome. Have a good one.

Best,

S