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 |
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 | - | ?? |
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];
LOAD Max(Rank) as MaxRank
Resident Temp;
Let vMaxRank= Peek('MaxRank');
SET I = 1;
LET vName = FieldValue('LeftDoc',$(I));
Table:
NoConcatenate
LOAD N°,[N° doc] as NDoc$(vName)
Resident Temp Where Rank=$(I);
FOR I=2 to $(vMaxRank)
LET vName = FieldValue('LeftDoc',$(I));
Join
LOAD N°,[N° doc] as NDoc$(vName)
Resident Temp Where Rank=$(I);
NEXT;
Left Join (Table)
LOAD N°,Sum(Montant) as Montant
Resident Temp
Group By N°;
Drop Table Temp;
Regards,
Antonio
Hi,
another solution might be:
Generic
LOAD N°,
'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