topic Re: How to store the values in a Table into a variable in QlikView

How to store the values in a Table into a variable

Mon, 27 Jun 2016 09:13:40 GMT

TagName	Fill A Time	Fill B Time	Fill C Time
Tag A	06:00
Tag A	06:05
Tag A	06:09
Tag B		06:02
Tag B		06:03
Tag B		06:05
Tag B		06:06
Tag C			06:03
Tag C			06:05
Tag C			06:07

can anyone, please help me, is there a way to get the last value for each field in the table..

To be clear, in the above situation, for each tag I will have lot of rows, but I am interested in the last row of each Tagname. Is there a way to get a last record for each value.

usually, we use Peak function to get the Filed value in a Table,we have to give the row number for accessing the value..

In my case, each tag will have more than one value and i will have no idea, which row number i should give in the peek function.

Is there a way to do it using peek function or do we have any other function..

please help me with this

Thank you

cleveranjos‌ hallquist_nate‌

Re: How to store the values in a Table into a variable

MK_QSL — Mon, 27 Jun 2016 09:16:07 GMT

What is the value you want to store?
I mean what value of Tag A.. What value for Tag B and What for Tag C?

Re: How to store the values in a Table into a variable

Anonymous — Mon, 27 Jun 2016 09:18:29 GMT

Hi Ganesh,

Calculate max value for each group:

Max:

LOAD

TagName,

RangeMax(Max(Fill A Time),Max(Fill B Time),Max(Fill C Time)) as MaxValue

RESIDENT YourTable

GROUP BY TagName;

Then, you can create the vars you need with a for:

For i = 0 to NoOfRows(Max)-1

LET vValue_$(i) = Peek('MaxValue',$(i),'Max');

Next i

Regards!!

Re: How to store the values in a Table into a variable

avinashelite — Mon, 27 Jun 2016 09:19:03 GMT

You could try with the MAX() Function in the front end like

vAMax= max([Fill A Time])

vBMax= max([Fill B Time])

vCMax= max([Fill C Time])

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 09:23:25 GMT

Hi Manish

Thanks for the quick reply

I like to store last value for each Tag.

I mean, Tag A : 06: 09

Tag B : 06: 06

Tag C : 06: 07

But, the above table is a sample table... I will not be sure that... Tag A will have only three values or anything like that..

But, I want the last value for each tag in the Table..

I hope, I am clear

Re: How to store the values in a Table into a variable

MK_QSL — Mon, 27 Jun 2016 09:25:35 GMT

vTagA = Time(Max(Time#([Fill A Time],'hh:mm')),'hh:mm')

vTagB = Time(Max(Time#([Fill B Time],'hh:mm')),'hh:mm')

vTagC = Time(Max(Time#([Fill C Time],'hh:mm')),'hh:mm')

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:00:12 GMT

HI Manish,

Thats really brilliant.. solution. But, I am getting an error because of my lack of knowledge in Time conversion, could you help me with that.. I tried like the following way

I am getting some error as " script line error"

actually i get the Time value in Timestamp format as "YYYY-MM-DD hh:mm:ss"..

That is the value of Fill A Time in the above Timestamp format..

I used these two expressions

LET vCurrentTime1=Time(Max(Time#([Fill A TIme],' YYYY-MM-DD hh:mm:ss')),'YYYY-MM-DD hh:mm:ss');

LET vCurrentTime1=Timestamp(Max([Fill A TIme]),'YYYY-MM-DD hh:mm:ss');

and also, i used your expressions as it is, but i am getting the same error

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:05:01 GMT

Hi Avinash

How are you??

When i use the above expression, I am getting Null value for the variable when i see in debug mode

but when i use the same expression in text, I am getting a value in float Format, as you know qlikview converts the date into a number format..

How to convert the time into a timestamp format of 'YYYY-MM-DD hh:mm:ss'

Thank you

Ganesh

Re: How to store the values in a Table into a variable

Anonymous — Mon, 27 Jun 2016 10:06:14 GMT

Hi Ganesh,

Have you seen my response?

You can create all variables that you need os script with a loop.

Regards!

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:10:33 GMT

Hi Manuel

Sorry for the late reply.. i have seen this.. but, i am trying this now...

Thank you

Re: How to store the values in a Table into a variable

avinashelite — Mon, 27 Jun 2016 10:19:03 GMT

I am fine , thanks you .

Try like this :

vAMax= Timestamp(max([Fill A Time]),'YYYY-MM-DD hh:mm:ss')

//if your getting the null value try with this condition

if(trim(len([Fill A Time]))>0,Timestamp(max([Fill A Time]),'YYYY-MM-DD hh:mm:ss'))

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:50:31 GMT

Hi Manuel

Can we use Resident keyword to access the table which we got by using Resident Keyword..

To be clear,, can we use Resident Keyword two Times?? I have already used Resident Keyword to get the Fill A Time, Fill B Time, Fill C Time...

I have used the resident keyword two times, than i got the error as "invalid Expression"..

Thank you

Ganesh

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:51:45 GMT

HI Avinash

i have used that already,, but didnt work

Thank you

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 10:57:06 GMT

But interestingly,, i am getting the correct value, when i use the expression in Text box

Re: How to store the values in a Table into a variable

avinashelite — Mon, 27 Jun 2016 11:02:46 GMT

what is the value your getting ??

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 11:04:53 GMT

In script, I am getting error

In text object.. Exact dateTime that i needed..

But, I want that datetime in a variable and than use it further

Re: How to store the values in a Table into a variable

Anonymous — Mon, 27 Jun 2016 11:07:04 GMT

Hi Ganesh,

You can use Resident ever you want to get data from a resident table.

I have used this code:

YourTable:
LOAD * INLINE [
    TagName, Fill A Time, Fill B Time, Fill C Time
    Tag A, 6:00, , 
    Tag A, 6:05, , 
    Tag A, 6:09, , 
    Tag B, , 6:02, 
    Tag B, , 6:03, 
    Tag B, , 6:05, 
    Tag B, , 6:06, 
    Tag C, , , 6:03
    Tag C, , , 6:05
    Tag C, , , 6:07
];



Max:
LOAD
     TagName,
     TagName as TagName2,
     RangeMax(Max([Fill A Time]),Max([Fill B Time]),Max([Fill C Time])) as MaxValue
RESIDENT YourTable
GROUP BY TagName;

For i = 0 to NoOfRows('Max')-1
     LET vNameVar = Replace(Peek('TagName2',$(i),'Max'),' ','');
     LET v$(vNameVar) = Time(Peek('MaxValue',$(i),'Max'),'hh:mm');
Next i

And this is the result:

Regards!!

Re: How to store the values in a Table into a variable

avinashelite — Mon, 27 Jun 2016 11:10:15 GMT

with the variable your not getting the values ?? create the variable in the front end and assign the expression you have used in the text box..that should give you the results and you could use this variable in future too ..

could you share your script ? to check how your defining the variable

Re: How to store the values in a Table into a variable

Mon, 27 Jun 2016 11:13:54 GMT

Hi Manuel

But i tried it by using the Resident keyword two times,, but I am getting the error as invalid expression,

I am getting the error exactly at the table name of max Table

Re: How to store the values in a Table into a variable

Anonymous — Mon, 27 Jun 2016 11:16:55 GMT

Hi Ganesh,

Could you share your code?

Have you added the Group by with all the fields that aren't on the aggregation expressions?

Regards!!