Dissociate a field a field into 2 field

[davidoxi]

Hello

I have this table :

N°	N° doc
1	FAA03
1	VVV03
2	FAA04
2	VVV04
3	FAA05
3	VVV06
4	VVV08
4	FAA05

I went to get this table :

N°	N° doc  FAA	N° doc VVV
1	FAA03	VVV03
2	FAA04	VVV04
3	FAA05	VVV06
4	VVV08	FAA05

[marcus_malinow · Accepted solution]

A simple way to do this:

_tmp:

LOAD * INLINE [

N°, N° doc

1, FAA03

1, VVV03

2, FAA04

2, VVV04

3, FAA05

3, VVV06

4, VVV08

4, FAA05

];

Result:

LOAD

N°,

[N° doc] as [N° doc FAA]

RESIDENT

_tmp

WHERE

left([N° doc], 3) = 'FAA'

;

LEFT JOIN (Result)

LOAD

N°,

[N° doc] as [N° doc VVV]

RESIDENT

_tmp

WHERE

left([N° doc], 3) = 'VVV'

;

DROP TABLE _tmp;

[antoniotiman]

Hi,

Temp:
LOAD *,Autonumber(RecNo(),N°) as Rank
FROM
"https://community.qlik.com/message/1327323"
(html, codepage is 1252, embedded labels, table is @1);
Table:
LOAD N°,[N° doc] as NDocF
Resident Temp Where Rank=1;
Join LOAD N°,[N° doc] as NDocV
Resident Temp Where Rank=2;
Drop Table Temp;

Regards,

Antonio

[davidoxi]

thank you for your help

But I have another question, if I have a table like:

N°	N° doc
1	FAA03
1	VVV03
2	FAA04
2	AAA23
2	VVV04
3	AAA23
3	VVV04
3	FAA05
3	VVV06
4	VVV08
4	FAA05

And I want to dissociate   in 3 or 4 fileds ??

like this :

N°	N° doc  FAA	N° doc VVV	N° doc VVV
1	FAA03	VVV03	_
2	FAA04	VVV04	AAA23
3	FAA05	VVV06	AAA23
4	VVV08	FAA05	_

[antoniotiman]

Why

Row 2 FAA04-VVV04-AAA23

and

Row 4 VVV08-FAA05

[antoniotiman]

Is it this the expected result ?

[davidoxi]

yes exctly,

how can I do??

[antoniotiman]

Temp:
LOAD *,Autonumber(Left([N° doc],3)) as Rank
FROM
"https://community.qlik.com/message/1327323"
(html, codepage is 1252, embedded labels, table is @3);
Table:
LOAD N°,[N° doc] as NDocF
Resident Temp Where Rank=1;
Join LOAD N°,[N° doc] as NDocV
Resident Temp Where Rank=2;
Join LOAD N°,[N° doc] as NDocA
Resident Temp Where Rank=3;
Drop Table Temp; 

[sunny_talwar]

One option could be this

Table:

LOAD *,

KeepChar([N° doc], 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as Field;

LOAD * INLINE [

    N°, N° doc

    1, FAA03

    1, VVV03

    2, FAA04

    2, AAA23

    2, VVV04

    3, AAA23

    3, VVV04

    3, FAA05

    3, VVV06

    4, VVV08

    4, FAA05

];

FinalTable:

LOAD Distinct N°

Resident Table;

FOR i = 1 to FieldValueCount('Field')

LET vField = FieldValue('Field', $(i));

Left Join (FinalTable)

LOAD N°,

[N° doc] as ['N° doc' $(vField)]

Resident Table

Where Field = '$(vField)';

NEXT

DROP Table Table;

[davidoxi]

thank you  pro