topic How to combine more than 1 array to 1 array? in QlikView

How to combine more than 1 array to 1 array?

Anonymous — Thu, 23 Jun 2011 01:42:43 GMT

I want to use this combined array in Load Script. The combined array come from more than 1 array(possiblevalues).

For example:

When I select a HMgStaffCode,I want to combine this HMgStaffCode value,MgStaffCode(possible values) and StaffCode(possible values) to a new array 'vStaffCode' with Macro.

-Load Script

SQL Select Period,StaffCode,MgStaffCode,HMgStaffCode,Sales from Rawdata

where staffcode in '$(vStaffCode)';

In SQL script, the format of $(vStaffCode) must be like ('T00123','F00236','S00634',......).

The format of StaffCode&MgStaffCode&HMgStaffCode just like T00123,F00236,S00634,......

How to do it?

Re: How to combine more than 1 array to 1 array?

chriscammers — Thu, 23 Jun 2011 03:21:19 GMT

I'm not going to pretend that I understand ALL of what you said in your question but I think the key to achieving your result is the Crosstable load and the CONCAT function. It creates a delimited list based on the values in a field over a group by.

Here is some code that illustrates how I would approach it.

//it sounds like your data is found in three different fields on the same table

//Crosstable stretches your results so they will fill the same field

ArrayTemp:

CrossTable(StaffCodeType, StaffCode, 1)

Load * Inline [

Period, StaffCode, MgStaffCode, HMgStaffCode

1, T00123, F00236, S00634

2, T00123, G00236, X00634

];

//Concatenate all the values and enhance the delimeter a bit

FinalArray:

Load

Chr(39) & Replace(Concat(Distinct StaffCode, ','),',',chr(39) & ',' & chr(39)) & Chr(39) as StaffCodeArray

Resident

ArrayTemp;

//assign the string to a variable

Let vStaffCode = peek('StaffCodeArray',0,'FinalArray');

//Use it in SQL

Load *;

SQL Select * from Whatever

Where StaffCode in ($(vStaffCode));

How to combine more than 1 array to 1 array?

Anonymous — Fri, 24 Jun 2011 01:54:52 GMT

Thank you for your reply.But I prefer to use it by Macro.

When I select a HMgStaffCode and run the Macro, the HMgStaffCode value, MgStaffCode(possible values) and StaffCode(possible values) (not only 1 field) can be combined into a array, and then reload data and reduce data(keep possible values).

Now I want to know how to combine HMgStaffCode, MgStaffCode,StaffCode possible values of these 3 fields into a array by Macro.

How to combine more than 1 array to 1 array?

chriscammers — Fri, 24 Jun 2011 02:41:44 GMT

Oh, I see

I'm not a guy who does many macros. Just to satisfy my curiosity and help others understand the issue could you explain what you are trying to accomplish with your Macro??

Thanks

Chris

How to combine more than 1 array to 1 array?

kaushiknsolanki — Fri, 24 Jun 2011 06:24:27 GMT

Hi,

I have answered something related question on below post.

http://community.qlik.com/message/126833#126833

Regards,

Kaushik Solanki