qlikview Chart Restrictions

[Anonymous]

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 !!!!

[tresesco]

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.

[vinieme12]

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

Dates in Set Analysis

Set Analysis: syntaxes, examples

[Anonymous]

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

[Anonymous]

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

[tresesco]

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

[vinieme12 · Accepted solution]

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)

[vinieme12]

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

[Anonymous]

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.

Thanks in Advance.

Regards,

Vigneshwaran.M.K

[Anonymous]

Hi Vineeth,

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

Regards,

Vigneshwaran.M.K