topic Re: Problem with last value in QlikView

Problem with last value

Mon, 22 Jul 2013 13:37:36 GMT

Hello,

I have a question for you:

I have a straight table with columns:

PHONE_CODE, KPI .. etc.

For every year-week I have a different kpi.

The problem is that for every PHONE_CODE, I have multiple lines, as I have multiple KPI values (multiple calendar dates).

How can I display only the last value of Kpi, the value of last week in selection?

Thank YOU!

Re: Problem with last value

kaushiknsolanki — Mon, 22 Jul 2013 13:42:25 GMT

Hi,

I am not clear about your problem.

Kindly explain with example and if possible post your qvw file.

Regards,

Kaushik Solanki

Re: Problem with last value

Mon, 22 Jul 2013 13:50:04 GMT

Hi,

I have :

PHONE_CODE, KPI, ..........

CODE1, 1 ............... (for 2012-1),

CODE1, 2 ............... (for 2012-2),

CODE1, 3............... (for 2012-3),

CODE1, 4 ............... (for 2012-4)

in straight table.

I want to have:

CODE1,4 in my table ->last value of KPI for year-week for a given CODE.

Re: Problem with last value

kaushiknsolanki — Mon, 22 Jul 2013 13:52:27 GMT

Hi,

Kindly share your application and also tell us what should be the output.

Regards,

Kaushik Solanki

Re: Problem with last value

Mon, 22 Jul 2013 14:03:28 GMT

Thank you very much for your help, but, unfortunatelly I cannot share the app..

What I want to achive : transform table from :

PHONE_CODE, KPI (kpi's for consecutive weeks)

CODE1, 1

CODE1, 2

CODE1,3

CODE1,4

into:

PHONE_CODE, KPI (kpi's for consecutive weeks)

CODE1,4

I want to display only the last value in time for KPI.

I tried last(kpi), but doesn't work-> 'error in calculation'

Re: Problem with last value

kaushiknsolanki — Mon, 22 Jul 2013 14:07:58 GMT

Hi,

If you have this table in script then you can try this.

TEMP:

Load Max(KPI),PHONE_CODE,PHONE_CODE&'-'&Max(KPI) as KEY

from xyz group by PHONE_CODE;

DATA:

Load *

from xyz

where exists(KEY,PHONE_CODE&'-'&KPI);

So what I am trying to do is find the max KPI for each of the PhoneCode and then load only data for them.

Regards,

Kaushik Solanki

Re: Problem with last value

Mon, 22 Jul 2013 14:20:59 GMT

Thx for reply, but I don't need the max of kpi..

Kpi can be: 1,2,3,4,5,2.

In my case the correct value will be 2, the last one. Not 5, the max one.

Re: Problem with last value

kaushiknsolanki — Mon, 22 Jul 2013 14:24:30 GMT

Hi,

Then try this code to get the last record of the table.

TEMP:

Load Distinct KPI,PHONE_CODE from xyz.

Let vLastKPI = peek('KPI',-1,'TEMP');

Let vLastPHONE = peek('PHONE_CODE',-1,'TEMP');

Data:

Load * from xyz where KPI = $(vLastKPI) and PHONE_CODE = $(vLastPHONE);

Drop table Temp;

Regards,

Kaushik Solanki