topic Re: Split a field in QlikView

Split a field

AmCh — Tue, 18 May 2021 16:27:50 GMT

Hi all,

I have the following table of fields as an example:

Field_1	Field_2	Field_3	Field_4
A	A1	...
A	A1
A	A2
B	B1
B	B2
B	B2
C	C1
C	C1
C	C2		...

I want to split Field_2 into subfields by values of Field_1, like:

Field_A

Field_B

Field_C

The end user can select from the new fields one value (as an example: A1,B1,C1).

Field_2 should highlight A1,B1,C1 (should recognize the choosen values from its subfields).

The end user can select now one or more than value from A1,B1,C1 in field_2.

Any help please?

Thanks in advance.

Re: Split a field

QFabian — Tue, 18 May 2021 18:19:36 GMT

Hi @AmCh , here you have an example.

Just wondering how to manage the last letter, this example works for A B C.

SourceData:
LOAD * INLINE [
Field_1, Field_2
A, A1
A, A1
A, A2
B, B1
B, B2
B, B3
C, C1
C, C1
C, C2
];

For vLetter = 65 to 67
Let vTable = chr(vLetter);
$(vTable):
Load
Field_1,
Field_2 as Field_$(vTable)

Resident SourceData
Where
Field_1 = '$(vTable)';

Re: Split a field

nsm1234567 — Tue, 18 May 2021 20:26:00 GMT

Hey there,

I'm a little confused what you're trying to do here, but have you tried doing a Generic load? Example attached

SOURCE_DATA:
LOAD * INLINE [
Field_1, Field_2, Field_3
A, A1, 1
A, A2, 2
A, A3, 3
B, B1, 4
B, B2, 5
B, B2, 6
C, C1, 7
C, C1, 8
C, C2, 9
];

GenericData:
Generic
LOAD Field_2 as Key,
Field_1 as Attribute,
Field_1 as Value
Resident SOURCE_DATA;

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericData' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo

For each vTableName in $(vListOfTables)

let vNewFieldName = 'Field_'&SubField('$(vTableName)','.',2);
Load Key as [$(vNewFieldName)],
SubField('$(vTableName)','.',2) as Field_1
Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

Re: Split a field

Saravanan_Desingh — Wed, 19 May 2021 01:54:12 GMT

Try this,

tab1: LOAD * INLINE [ Field_1, Field_2, Field_3 A, A1, 1 A, A2, 2 A, A3, 3 B, B1, 4 B, B2, 5 B, B2, 6 C, C1, 7 C, C1, 8 C, C2, 9 ]; tab2: Generic LOAD 1, 'Field_'&Field_1, Field_2 Resident tab1; Drop Table tab1; Drop Field [1];

Re: Split a field

Saravanan_Desingh — Wed, 19 May 2021 01:54:54 GMT

Output:

Re: Split a field

AmCh — Wed, 19 May 2021 12:16:38 GMT

Hi @nsm1234567 ,

thanks for you reply.

There are unfortunately two problems:

1. I can select only from Field_A, Field_B or Field_C.

The objective: I should be able to select one value from each Field_ (subfield).

2. By selecting A1 from Field_A as an example, Field_2 recognize that one value from Field_A and not exactly which value,;in this case A1.

The objective: Field_2 should recognize which value from each subfield is selected.

Any help please?

Thanks in advance.

Re: Split a field

AmCh — Wed, 19 May 2021 12:22:37 GMT

Hi @Saravanan_Desingh ,

thanks for your answer.

The problem hier:

The original fields are not preserved.

The objective: the respectively selected values in subfields should be recognized (highlighted) in the original fields.

Any help please?

Thanks in advance.

Re: Split a field

AmCh — Wed, 19 May 2021 12:29:40 GMT

Hi @QFabian ,

thanks for your try.

There are unfortunately two problems:

1. I can select only from Field_A, Field_B or Field_C. I couldn't select simultaneously values from different subfields.

The objective: I should be able to select one value from each Field_ (subfield).

2. By selecting A2 from Field_A as an example, Field_2 recognize that one value from Field_A and not exactly which value,;in this case A2.

The objective: By selecting A1, B1, C1 as an example from subfields of Field_2 , this one (Field_2) should highlight exactly A1,B1,C1.

-> Field_2 is the source for its subfields and 'collect' later the selections from all its subfields.

Any help please?

Thanks in advance.