Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Find max ID according another column

Hi for everyone, I need an advice, becauase I am a novice in QV. I have a following table: ID    Environment 1      PR 2      PR 3      PR 1      CK 2      CK 1      RC 2      RC 3      RC I have this table stored i QVD file and I need to find the maxID by Environment: PR  3 CK  2 RC  3 Is here any function like peek where is possible to define find max ID per Environment or I have to do this? ID: LOAD max(ID) as MaxID,     Environment     FROM xxxxxxxxx\ID.QVD (QVD) GROUP BY Environment; LET MaxIDRC = FieldValue('MaxID',fieldindex('Environment','RC')); LET MaxIDPR = FieldValue('MaxID',fieldindex('Environment','PR')); LET MaxIDCK = FieldValue('MaxID',fieldindex('Environment','CK')); This is good solution but I need to load from QVD twice in incremental load. The wanted solution is load all from QVD, find the max ID per Environment and Concatenate with another table where max ID will be used. Thank for you replies!!!

9 Replies
Highlighted
Not applicable

Re: Find max ID according another column

Ups...that is bad display of defined table. I will try again

Not applicable

Re: Find max ID according another column

This is better....so again.

Hi for everyone, I need an advice, becauase I am a novice in QV. I have a following table:

ID.PNG

I have this table stored i QVD file and I need to find the maxID by Environment: PR  5 CK  3 RC  4


Is here any function like peek where is possible to define find max ID per Environment or I have to do this? I


ID_QV.PNG



This is good solution but I need to load from QVD twice in incremental load. The wanted solution is load all from QVD, find the max ID per Environment and Concatenate with another table where max ID will be used.


Thank for you replies!!!

Highlighted

Re: Find max ID according another column

Not sure if you specifically need the MaxID to be assigned to a variable, but this can be an alternative:

Table:

LOAD * INLINE [

    ID, Environment

    1, PR

    2, PR

    3, PR

    4, PR

    5, PR

    1, CK

    2, CK

    3, CK

    1, RC

    2, RC

    3, RC

    4, RC

];

Join(Table)

LOAD Environment,

  Max(ID) as MaxID

Resident Table

Group By Environment;



HTH

Best,

Sunny

Highlighted
Not applicable

Re: Find max ID according another column

Thanks for your reply, but I need anythnig else. Hope this helps for better understanding.

ID_QV.PNG

Becuase I have a lot of data in QVD file, I dont want to loaded twice.

Thanks

Highlighted

Re: Find max ID according another column

I don't think there is a function available to do what you want to do, but to avoid loading the QVD twice why don't you use the resident load of your first load from QVD to do the calculations. May be like this:

ID:

LOAD ID,

          Prostredi,

          Mnozstvi

FROM yourQVD.qvd (qvd);

ID1:

LOAD Max(ID) as MaxID,

          Prostredi

Resident ID

Group By Prostredi;

LET MaxIDRC = FieldValue('MaxID', FieldIndex('Prostredi', 'RC'));

LET MaxIDPR = FieldValue('MaxID', FieldIndex('Prostredi', 'PR'));

LET MaxIDCK = FieldValue('MaxID', FieldIndex('Prostredi', 'CK'));

DROP Table ID1;

and then continue with the rest of your script...

Benefits of this would be: You will load the qvd only one time and that too will be the optimized load (in contrast to your first load where the ID1 load was not optimized load.

HTH

Best,

Sunny

Highlighted
Not applicable

Re: Find max ID according another column

I hoped there is any function for it, but it seems not. OK thank you for your advice. I avoid finding maxID from Resident because it is too slow if I have about 200Mio rows and my solution (direct from QVD) is faster 100x.


Here is the evidence https://community.qlikview.com/thread/158954


Peter

Highlighted

Re: Find max ID according another column

I guess you are right, this is the 2nd time I have came across this argument that Resident Load are slower than a QVD load. Need to do some more research on this topic. But thanks for your insights

Best,

Sunny

Highlighted
Not applicable

Re: Find max ID according another column

Please let me know if you find out anything helpful

Here is some research “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

Thanks a lot, Regards, Peter

Highlighted

Re: Find max ID according another column

You know what, I actually happen to come across this, but since I never used it I forgot about it. So I guess what you are doing is the best you can do. I don't know of any inbuilt function. But having said that my knowledge of QlikView is limited and someone else might one come in and give a better answer.

Best,

Sunny