topic Re: one sheet with multiple tables within in Connectivity & Data Prep

one sheet with multiple tables within

Sierk — Thu, 23 Dec 2021 15:17:29 GMT

Dear Anybody,

I have troubles uploading the attached Excel sheet in the right matter. I would like to have a row with the unique number of the one who worked in week 1 and in the same row the name of the client where he/she worked for. The people who worked for one client differs every time. I tried to use the peek function. But I can only return one value. I cannot repeat this command.

I tried:

IF(LEN("Naam")>1 and (len(peek("Naam")<0)), Peek(Test)) as Test

Uren overzicht				Week 1

Unique number	Naam	Worked hours	Sick
Client A
22473	Pietje	32	0	0
22470	Klaasje	40	0	0
Client B
21861	Tom	40	0	0
Client C
15339	Rob	40	0	0
Client D
19397	Anne	39	0	0
20913	Roos	38	0	0
50734	Jellen	40	0	0
18692	Kees	16	0	0
20816	Tom	16	0	0
22010	Jaap	16	0	0
50737	Kees	32	0	0
50735	Cheesy	40	0	0
18464	Remco	40	0	0
11717	Rarko	40	0	0
20721	Jesse	29	0	0
21476	Geert	40	0	0
16340	Annie	40	0	0
18411	Annemarie	39	0	0
22481	Blabla	39	0	0
Client E
16956	X	24	0	0
17327	Y	36,5	0	0
18182	Z	40	0	0
15442	D	24	0	0
9316	B	40	0	0
20248	E	38,5	0	0
20512	F	44	0	0
20512	G	40	0	0

Re: one sheet with multiple tables within

MayilVahanan — Fri, 29 Jan 2021 09:13:55 GMT

Hi @Sierk

I think, you want to keep client name as separate column?. In that case, try like below

Temp:

LOAD *, if(Len(Trim(Naam))=0, [Unique number], Peek('ClientName')) as ClientName from yoursource;

NoConcatenate

Final:
Load * Resident Temp Where IsNum([Unique number]);

DROP Table Temp;

Re: one sheet with multiple tables within

Sierk — Fri, 29 Jan 2021 14:54:13 GMT

Dear Mr Vahanan,

It worked! Thank you very much! I thought I tried everything already but apparently not!

Kind regards,

Sierk