topic Re: Split a Table in Qlik Automate

Split a Table

Amina — Wed, 08 Jan 2025 12:25:42 GMT

Hello,

I have a table containing rows with sub-rows. First I need to filter on a keyword (example T) then add the sub-rows.

Example Table:

PP	PS	SPS	ART	ID
111	10		123456	T
111	10	01	333333
111	10	02	333354
111	20		544656
222	10		255455	B
222	10	01	654844
222	10	02	544444

So the aim is to filter only on lines with ID = T + sub-lines.

PP	PS	SPS	ART	ID
111	10		123456	T
111	10	01	333333
111	10	02	333354

how I can do this with the script in Qlik sense.

Thank you for your help

Re: Split a Table

Or — Thu, 09 Jan 2025 11:25:39 GMT

Load PP,PS,SPS,ART,ID, PP & PS as 'Key'

From YourTable

Where ID = 'T';

Concatenate

Load

Load PP,PS,SPS,ART,ID

From YourTable

Where ID is null and exists(Key,PP&PS);

Re: Split a Table

eddyvargas — Wed, 08 Jan 2025 13:02:28 GMT

First of all complete the blanks cells and then load only what you need

TempTable:
load
*,RowNo()
Inline [
PP,PS,SPS,ART,ID
111,10,,123456,T
111,10,01,333333
111,10,02,333354
111,20,,544656
222,10,,255455,B
222,10,01,654844
222,10,02,544444
];

FinalTable:
NoConcatenate
LOAD PP,PS,SPS,ART,
If(Len(Trim(ID)) > 0, ID, Peek('ID')) AS ID
RESIDENT TempTable
ORDER BY [RowNo()];

Drop Table TempTable;

NoConcatenate
LOAD *
Resident FinalTable
Where ID='T';

Drop Table FinalTable;

Re: Split a Table

Amina — Thu, 09 Jan 2025 09:05:24 GMT

Thank you for your reply,
using your solution, I also have PS= 20 in the result.