topic Re: Find max ID according another column in QlikView

Find max ID according another column

Wed, 15 Apr 2015 18:51:30 GMT

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

Re: Find max ID according another column

Wed, 15 Apr 2015 18:54:43 GMT

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

Re: Find max ID according another column

Wed, 15 Apr 2015 18:59:00 GMT

This is better....so again.

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

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

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

Re: Find max ID according another column

sunny_talwar — Wed, 15 Apr 2015 19:09:15 GMT

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

Re: Find max ID according another column

Wed, 15 Apr 2015 19:31:29 GMT

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

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

Thanks

Re: Find max ID according another column

sunny_talwar — Wed, 15 Apr 2015 19:39:55 GMT

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

Re: Find max ID according another column

Wed, 15 Apr 2015 19:59:42 GMT

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

Re: Find max ID according another column

sunny_talwar — Wed, 15 Apr 2015 20:36:12 GMT

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

Re: Find max ID according another column

Thu, 16 Apr 2015 07:05:21 GMT

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

Re: Find max ID according another column

sunny_talwar — Thu, 16 Apr 2015 15:15:22 GMT

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