Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davidoxi
Contributor III
Contributor III

Dissociate a field a field into 2 field

Hello

I have this table :

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

I went to get this table :

N° doc  FAAN° doc VVV
1FAA03VVV03
2FAA04VVV04
3FAA05VVV06
4VVV08FAA05
1 Solution

Accepted Solutions
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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;

View solution in original post

12 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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
Master III
Master III

Hi,

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

Regards,

Antonio

davidoxi
Contributor III
Contributor III
Author

thank you for your help

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

N° doc
1FAA03
1VVV03

2

FAA04
2AAA23
2VVV04
3AAA23
3VVV04
3FAA05
3VVV06
4VVV08
4FAA05

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

like this :

N° doc  FAAN° doc VVVN° doc VVV
1FAA03VVV03_
2FAA04VVV04AAA23
3FAA05VVV06AAA23
4VVV08FAA05_

antoniotiman
Master III
Master III

Why

Row 2 FAA04-VVV04-AAA23

and

Row 4 VVV08-FAA05

antoniotiman
Master III
Master III

Is it this the expected result ?

davidoxi
Contributor III
Contributor III
Author

yes exctly,

how can I do??

antoniotiman
Master III
Master III

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° doc] as NDocF
Resident Temp Where Rank=1;
Join LOAD ,[N° doc] as NDocV
Resident Temp Where Rank=2;
Join LOAD ,[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;

Capture.PNG

davidoxi
Contributor III
Contributor III
Author

thank you  pro