topic Handling Arrays in QlikView

Handling Arrays

Thu, 27 Feb 2014 07:16:08 GMT

Hi,

is it possible in Qlikview to build arrays or array-like structures? I want to compare some values form a table and if they meet certain criteria, I want to store this values. So an array was the first thing that I thought of. I will use it in an iterative process, so I am looking for something, where I can store values repeadedly, without just overwriting it.

And after creating such an array, how can i write it´s values into a table?

best regards,

Christian

Re: Handling Arrays

sunilkumarqv — Thu, 27 Feb 2014 07:25:47 GMT

using macro it's possible to do

try like this

Sub Test

strOfNames = """Name 1"",""Name 2"",""Name 3"""

msgbox strOfNames

End Sub

Re: Handling Arrays

whiteline — Thu, 27 Feb 2014 07:31:21 GMT

Hi.

Transform your logic into the terms of tables (Map + Reduce logic for example).

You can use Autogenerate() + concatenate statements to add the rows one by one.

Then you can use mapping with ApplyMap/lookup and group by.

Re: Handling Arrays

jonathandienst — Thu, 27 Feb 2014 08:15:38 GMT

Not sure why you want to do this, but assuming that you have a valid reason, here goes. To create the array variable:

Let aListLength = NoOfRows('myTable');

For zi = 0 To aListLength - 1

Let aList$(zi) = Peek('myField', zi, 'myTable');

This builds variables: aList1, aList2, ... aListn from mField in myTable.

To load into table:

For zi = 0 To aListLength - 1

LOAD '$(aList$(zi))' As newField

AUTOGENERATE 1;

HTH

Jonathan

Re: Handling Arrays

Thu, 27 Feb 2014 08:42:31 GMT

Thank you for your reply.

I´m doing this for the following reason:

I have a scenario, where I need to extract a certain timespan from a list of timestamps. I need to compare 2 timestamps, if the difference between this 2 is smaller than a certain amount of time I need to safe the first stamp and go on to compare the next stamps.

If the condition is not matched I will just go on to the next stamp and create a new list where I store the stamps that match my condition and so on. In the end I will (hopefuly) have a number of lists that i can store into tables to do further evaluation with it.

The array would help me to store the extracted timestamps that match my condition.

I am new to Qlik, so I thought of the solutions I would use when working with a regular coding language.

I hope I could make clear what I am trying to achive.

thanks,

Christian

Re: Handling Arrays

jonathandienst — Thu, 27 Feb 2014 09:55:24 GMT

I would expect that a data based solution (using LOADs and Resident LOADs) would perform far better than moving the data into variables.

I don't fully understand your requirements, but I am confident that it can be done in script. To clarify:

I assume the timestamps are coming from a field in your source data
Is there some sort of ID or key field to identify which timestamps are which?
Starting with the earliest timestamp, you need all the consecutive timestamps within a certain period from the earliest - is that correct?
Please explain in more details the 'next list'. Does this start with timestamp 2, or the first timestamp not in the first list?
The output will depend on what sort of further processing you need to do. So you might want to explain that as well.

Regards

Jonathan

Re: Handling Arrays

Thu, 27 Feb 2014 10:44:49 GMT

The company I´m doing this for wants to measure certain picks/timeframe.

I have:

- timestamps (when the pick happened)

- employee (who picked)

- key

The above are coming form a SQL Database, there is a key to identify each row.

I need each timestamp in comparison to its proccessor.

The reason is the following:

The people who pick do different stuff then just picking. The company wants to identify the timeframe in that a picker was "actively" picking. Then for this Timeframe they want to measure the amount of picks/timeframe.

So if two consecutive picks are more then 5 nminutes apart this means the picker is doing other things not picking anymore. This means the timeframe is not relevant for the evaluation.

So each timestamp is just in relation to its following stamp. If two stamps are within 5 minutes the picker was actively picking. I always analyze pairs of stamps (stamp 1&2 ,2&3, 3&4, 4&5...). This goes on, until two stamps are further apart then 5 minutes. The result is an amount of timestamps, where each stamp is within 5 minutes of its following stamp. I now can say: In a span of xx minutes (first stamp, to last stamp) happend YY (the number of timestamps I collected).

You might ask now: Why dont you just compare all picks to the whole period of time and get picks/day?

Because, the company wants to evaluate the picks per hour in an "active" picking phase. So that the whole time where the did other things except picking is not evaluated.

thanks for your kind reply

Re: Handling Arrays

Thu, 27 Feb 2014 15:00:05 GMT

Here is a Version I created and it looks like it´s working.

The Main Problem I am attacking with this is, that there are no Arrays in Qlikview and I needed to find a Way to safe a Value for further Processing.

here is what I came up with:

Before I started, I sorted the Table with a LOAD and order by, so that all the Timestamps were sorted.

So what this does is basicaly, to extract my desired Values from a table, match them against a condition ( in my case timestamps that were less then 5 minutes apart) and safe them in a variable to store them into a new table.

I hope other people having similar problems will find this helpful. Feel free to ask any Questions.

cheers,
Christian