Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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
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 | _ |
Why
Row 2 FAA04-VVV04-AAA23
and
Row 4 VVV08-FAA05
Is it this the expected result ?
yes exctly,
how can I do??
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;
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;
thank you pro