Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
davidoxi
New 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
Valued Contributor III

Re: Dissociate a field a field into 2 field

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;

12 Replies
marcus_malinow
Valued Contributor III

Re: Dissociate a field a field into 2 field

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
Honored Contributor III

Re: Dissociate a field a field into 2 field

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
New Contributor III

Re: Dissociate a field a field into 2 field

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
Honored Contributor III

Re: Dissociate a field a field into 2 field

Why

Row 2 FAA04-VVV04-AAA23

and

Row 4 VVV08-FAA05

antoniotiman
Honored Contributor III

Re: Dissociate a field a field into 2 field

Is it this the expected result ?

davidoxi
New Contributor III

Re: Dissociate a field a field into 2 field

yes exctly,

how can I do??

antoniotiman
Honored Contributor III

Re: Dissociate a field a field into 2 field

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; 

Re: Dissociate a field a field into 2 field

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
New Contributor III

Re: Dissociate a field a field into 2 field

thank you  pro

Community Browser