Qlik Community

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 
Search instead 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

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)

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

View solution in original post

8 Replies
Highlighted

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,

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

Tag.png

A new row has to be created with Borrow date as Today(). and borrow Qty as Pending Fulfillment here 6-4.


Thanks

Highlighted

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)

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

Highlighted
Not applicable

Re: straight Table

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.

Highlighted

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)

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

View solution in original post

Highlighted
Not applicable

Re: straight Table

Thank Yu..

Highlighted

Re: straight Table

Your very welcome. Have a good one.

Best,

S