How to combine more than 1 array to 1 array?

[Anonymous]

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?

[chriscammers]

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));

[Anonymous]

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.

[chriscammers]

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

[kaushiknsolanki · Accepted solution]

Hi,

    I have answered something related question on below post.

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

Regards,

Kaushik Solanki