topic Re: Create array from field values in QlikView

Create array from field values

kangaroomac — Tue, 02 Feb 2016 05:23:28 GMT

Good Day,

Has anyone created a field with an array of values?

Below is script that demonstrates what I'm trying to achieve.

The desired output is for [Inner Table] to have the following:

Inner Code	Inner Value
1	A, B, C
2	D, E, F
3	G, H, I

[Data_Table]:
load * inline [
Code, Value
1, A
1, B
1, C
2, D
2, E
2, F
3, G
3, H
3, I
];

For Each x in FieldValueList('Code')

    Let vCurrentCode = $(x);

    [Outer Table]:
    LOAD
     '$(x)' as "Outer Code"
    AutoGenerate 1;


    For Each y in FieldValueList('Code')

        Let ConcatenatedValue = ConcatenatedValue & ' ' & Peek('Value',y,'Data_Table') & ',';

        [Inner Table]:
              LOAD
              '$(x)' as "Inner Code",
              '$(ConcatenatedValue)' AS "Inner Value"
        AutoGenerate 1
             Where
                   $(x) = $(vCurrentCode);

        Let content = Null;

    Next y

Next x

Re: Create array from field values

settu_periasamy — Tue, 02 Feb 2016 05:35:31 GMT

May be try this..

[Data_Table]:

load * inline [

Code, Value

1, A

1, B

1, C

2, D

2, E

2, F

3, G

3, H

3, I

];

NoConcatenate

New:

LOAD Code,Concat(Value,',') as Inner_Value Resident [Data_Table] Group by Code;

DROP Table [Data_Table];

Re: Create array from field values

PrashantSangle — Tue, 02 Feb 2016 05:38:37 GMT

Hi,

try below script

[Data_Table]:

load * inline [

Code, Value

1, A

1, B

1, C

2, D

2, E

2, F

3, G

3, H

3, I

];

Noconcatenate

Final:

Load Code,

Concat(Value,' ,') as InnerValue

Resident [Data_Value]

Group By Code;

Drop table [Data_Value];

Regards,

Re: Create array from field values

kangaroomac — Tue, 02 Feb 2016 05:41:01 GMT

Thanks Max,

That solved it.

Re: Create array from field values

Anonymous — Tue, 02 Feb 2016 05:52:22 GMT

may be helpful for you?

values from field to array by macro

Using Array to Identify Modified Rows in Qlikview Table | Smart ERP Solutions