Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
vigneshwaranmk
New Contributor III

qlikview Chart Restrictions

Hi All,

    I would like to restrict the data coming into an chart in qlikview. The Chart is an Straight Table and i am representing some 20 columns in that. The Thing is that one order can be reflected multiple times in the DB since i am fetching Transactional Tables i want to show only once. Say suppose there is an order No which was shown yesterday with an combinations of some line Numbers ( One Order can have multiple Line Numbers). The Same order with same line numbers are again updated today then the old one should be omitted and only today's rows has to be shown. Please note that the status will change which is an different  column because of which only we will get the new rows will come in the DB. The Problem is that this restriction has to be done in Straight Table level and not on Script Level. I have tried Before Function which doesn't seems to be providing the required results. Kindly suggest some other ideas. Thanks in Advance !!!!

1 Solution

Accepted Solutions
Highlighted
vinieme12
Esteemed Contributor II

Re: qlikview Chart Restrictions

Ok, then create a flag in your load script using Autonumber() to flag the latest record

for example:

Data:

LOAD *,AutoNumber(ID&Date,ID)as instanceID INLINE [

    ID, Date

    a,22-10-2017

    b,22-10-2017

    c,22-10-2017

    d,22-10-2017

    e,22-10-2017

    a,23-10-2017

    b,23-10-2017

];

and in chart use below expresion: only latest records for a and b will be shown

=if(instanceID=max(TOTAL <ID> instanceID),1)

View solution in original post

9 Replies
Highlighted
MVP
MVP

Re: qlikview Chart Restrictions

It seems that you need to get the recent most status/report dynamically. If I am right, you could probably use firstsortedvalue() like : =FirstSortedValue( StatusField, -DateField)

Assuming, you have OrderNo in chart dimension. If this doesn't work, try to share your sample qvw.

Highlighted
vinieme12
Esteemed Contributor II

Re: qlikview Chart Restrictions

why not restrict the records with set analysis to show rows for only todays date then?

Dates in Set Analysis

Set Analysis: syntaxes, examples

Highlighted
vigneshwaranmk
New Contributor III

Re: qlikview Chart Restrictions

Hi,

  Thanks for your answer. But i dont need to restrict the data to one day. What i need to do is to check if the current data matches with the historical data and represent it only once. If it does not match with the historical data then i will represent only the current data. It can also be of yesterday or day before yesterday or today it does not matter as long it is getting represented only once.

Thanks Again.

Regards,

Vigneshwaran.M.K

Highlighted
vigneshwaranmk
New Contributor III

Re: qlikview Chart Restrictions

Hi,

  Thanks for your response but it is not working out in my case. What i need to do is create an key by the order No - Line No - Product ID - Shipment Date And ETD Date which i have done. This same Key could come on another day based on difference in any other column other than this. If that is the case then it should be represented only once and that too the latest change should be that one. I will try and post an sample in some time as i am not allowed to share the client information.

Thanks in Advance.

Regards,

Vigneshwaran.M.K

Highlighted
MVP
MVP

Re: qlikview Chart Restrictions

We could help better if you share a sample qvw to work upon.

Highlighted
vinieme12
Esteemed Contributor II

Re: qlikview Chart Restrictions

Ok, then create a flag in your load script using Autonumber() to flag the latest record

for example:

Data:

LOAD *,AutoNumber(ID&Date,ID)as instanceID INLINE [

    ID, Date

    a,22-10-2017

    b,22-10-2017

    c,22-10-2017

    d,22-10-2017

    e,22-10-2017

    a,23-10-2017

    b,23-10-2017

];

and in chart use below expresion: only latest records for a and b will be shown

=if(instanceID=max(TOTAL <ID> instanceID),1)

View solution in original post

Highlighted
vinieme12
Esteemed Contributor II

Re: qlikview Chart Restrictions

also make sure your data is sorted by the order number and order date

Highlighted
vigneshwaranmk
New Contributor III

Re: qlikview Chart Restrictions

Hi Tresesco,

   Please find attached an sample file which  have put some dummy data. Here the first five columns  should be the key if you can see first row and fifth row all columns are same except ETA which has an different date. The same is the case for all other three as well. Only the latest should come. The problem is that i should make this restriction at chart level as i have some more objects also and in the data model all these comes from different tables where i have created an star schema.Chart Sample Data.png

Thanks in Advance.

Regards,

Vigneshwaran.M.K

Highlighted
vigneshwaranmk
New Contributor III

Re: qlikview Chart Restrictions

Hi Vineeth,

    I was able to achieve the same based on your idea. Thanks for the info.

Regards,

Vigneshwaran.M.K