12 Replies Latest reply: Aug 21, 2017 5:44 PM by Marco Wedel

# Dissociate a field a field into 2 field

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
• ###### Re: Dissociate a field a field into 2 field

A simple way to do this:

_tmp:

N°, N° doc

1, FAA03

1, VVV03

2, FAA04

2, VVV04

3, FAA05

3, VVV06

4, VVV08

4, FAA05

];

Result:

N°,

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

RESIDENT

_tmp

WHERE

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

;

LEFT JOIN (Result)

N°,

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

RESIDENT

_tmp

WHERE

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

;

DROP TABLE _tmp;

• ###### Re: Dissociate a field a field into 2 field

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 _

• ###### Re: Dissociate a field a field into 2 field

Why

Row 2 FAA04-VVV04-AAA23

and

Row 4 VVV08-FAA05

• ###### Re: Dissociate a field a field into 2 field

Is it this the expected result ?

• ###### Re: Dissociate a field a field into 2 field

yes exctly,

how can I do??

• ###### Re: Dissociate a field a field into 2 field

Temp:
FROM
"https://community.qlik.com/message/1327323"
(html, codepage is 1252, embedded labels, table is @3);
Table:
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

thank you  pro

• ###### Re: Dissociate a field a field into 2 field
 N° N° doc Montant(\$) rraa20 FAA04 789 rrt730 AAA23 152 rrt730 FAA05 785 rrt730 VVV04 365 rrt730 VVV06 193 rrtt10 FAA03 846 rrtt10 PPP08 579 rrtt10 VVV03 246 rtta20 AAA23 494 rtta20 VVV04 100

I went to get this table (object : straight table ) :

 N° NDocF NDocV NDocA NDocP aggr(sum(montant),N°) ??? rraa20 FAA04 - - - ?? rrt730 FAA05 VVV04 AAA23 - ?? rrt730 FAA05 VVV06 AAA23 - ?? rrta40 FAA08 VVV08 - PPP08 ?? rrtt10 FAA03 VVV03 - PPP08 ?? rtta20 - VVV04 AAA23 - ??
• ###### Re: Dissociate a field a field into 2 field

Hi Sahra,

try this

Temp:
LOAD *,Left([N° doc],3) as LeftDoc,Autonumber(Left([N° doc],3)) as Rank
Inline [
N°, N° doc, Montant
rraa20, FAA04, 789
rrt730, AAA23, 152
rrt730, FAA05, 785
rrt730, VVV04, 365
rrt730, VVV06, 193
rrtt10, FAA03, 846
rrtt10, PPP08, 579
rrtt10, VVV03, 246
rtta20, AAA23, 494
rtta20, VVV04, 100]
;

Resident Temp;
Let vMaxRank= Peek('MaxRank');

SET I = 1;
LET vName = FieldValue('LeftDoc',\$(I));

Table:
NoConcatenate
Resident Temp Where Rank=\$(I);

FOR I=2 to \$(vMaxRank)
LET vName = FieldValue('LeftDoc',\$(I));
Join
Resident Temp Where Rank=\$(I);
NEXT;
Left Join (Table)
Resident Temp
Group By ;
Drop Table Temp;

Regards,

Antonio

• ###### Re: Dissociate a field a field into 2 field

One option could be this

Table:

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

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:

Resident Table;

FOR i = 1 to FieldValueCount('Field')

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

Left Join (FinalTable)

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

Resident Table

Where Field = '\$(vField)';

NEXT

DROP Table Table;

• ###### Re: Dissociate a field a field into 2 field

Hi,

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

Regards,

Antonio

• ###### Re: Dissociate a field a field into 2 field

Hi,

another solution might be:

```Generic
'N° doc '&Left([N° doc],3),
[N° doc]
FROM [https://community.qlik.com/thread/271549] (html, codepage is 1252, embedded labels, table is @5);
```

hope this helps

regards

Marco