topic Re: Consolidate rows in QlikView

Consolidate rows

Bob71 — Sat, 16 Nov 2024 20:38:01 GMT

Hello everyone,

I'm using the load statement

LOAD id, tel

FROM table1;

I need that table1

turning in table2

How can achieve that?

Thanks in advance

marcus_sommer — Wed, 19 Jun 2019 11:38:22 GMT

I think this will be helpful: The-Generic-Load

- Marcus

sunny_talwar — Wed, 19 Jun 2019 11:48:08 GMT

Are you looking to do this in script?

Bob71 — Wed, 19 Jun 2019 12:08:39 GMT

Yes, if it is possible

Bob71 — Wed, 19 Jun 2019 12:16:07 GMT

Thanks, I'm going to read the article

saar_lorena — Wed, 19 Jun 2019 12:27:07 GMT

HI, try this:

Base:
LOAD * INLINE [
id, Tel
1, 1100
1, 2200
1, 3300
2, 4400
2, 5500
3, 6600
4, 7700
4, 8800
4, 9900
];

Reg_By_Id:
Load id, Count(Tel) as Counters, Concat(Tel, '|') as TelS Resident Base Group by id;

Max_Counter:
Load max(Counters) as maxTels Resident Reg_By_Id;

let vMaxTels = peek('maxTels',-1);

Base_Multiple_Tels:
load id, SubField(TelS,'|',1) as Tel1 Resident Reg_By_Id;

for i = 1 to $(vMaxTels)

left join(Base_Multiple_Tels)
load id, SubField(TelS,'|',$(i)) as Tel$(i) Resident Reg_By_Id;

next;

MindaugasBacius — Wed, 19 Jun 2019 12:33:29 GMT

I would do something like this:

tmp:
LOAD id,
Tel,
'Tel' & Autonumber(RowNo(), id) as number
FROM
[https://community.qlik.com/t5/New-to-QlikView/Consolidate-rows/m-p/1593418#M376678]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

The results in Pivot table:

Bob71 — Thu, 20 Jun 2019 09:01:56 GMT

In combination with the instructions in Generic Load, I've found the solution.

Thanks to all!