How to format given unstructured excel data ?

[shekhar_analyti]

Hi All ,

How to format below unstructured excel data ?

INPUT 

ID	NAME	REGION	WEIGHTAGE
SALES
ID1	NAME1	R1	0.44074642
ID2	NAME2	R2	0.10343234
ID3	NAME3	R3	0.4314422
ID4	NAME4	R4	0.45019801
ID5	NAME5	R5	0.37139072
ID6	NAME6	R6	0.81501561
ID7	NAME7	R7	0.55574406
ID8	NAME8	R8	0.55555429
PR
ID	NAME	REGION	WEIGHTAGE
ADHOCS
ID9	NAME9	R9	0.23475478
ID10	NAME10	R10	0.54411475
ID11	NAME11	R11	0.58992939
ID12	NAME12	R12	0.04174845
LEGAL
ID	NAME	REGION	WEIGHTAGE
TEMP
ID13	NAME13	R13	0.06142142
ID14	NAME14	R14	0.79227561
ID15	NAME15	R15	0.90962549

OUTPUT 

ID	NAME	REGION	WEIGHTAGE	TEAM	TYPE
ID1	NAME1	R1	0.130993109	SALES
ID2	NAME2	R2	0.661050116	SALES
ID3	NAME3	R3	0.972663368	SALES
ID4	NAME4	R4	0.413523484	SALES
ID5	NAME5	R5	0.110618327	SALES
ID6	NAME6	R6	0.413701376	SALES
ID7	NAME7	R7	0.503030479	SALES
ID8	NAME8	R8	0.476772841	SALES
ID9	NAME9	R9	0.44600587	PR	ADHOCS
ID10	NAME10	R10	0.274699848	PR	ADHOCS
ID11	NAME11	R11	0.905506174	PR	ADHOCS
ID12	NAME12	R12	0.739754741	PR	ADHOCS
ID13	NAME13	R13	0.4320139	LEGAL	TEMP
ID14	NAME14	R14	0.962760651	LEGAL	TEMP
ID15	NAME15	R15	0.711687606	LEGAL	TEMP

Thanks & Regards

Shekar

[Gysbert_Wassenaar]

Something like this perhaps:

tmpdata:
LOAD
    If(Len(Trim(NAME))=0,ID, Peek(TYPE)) as TYPE,
    ID,
    NAME,
    REGION,
    WEIGHTAGE
FROM
    [D:\temp\c1546407 - EXCEL HANDALING.xlsx]
    (ooxml, embedded labels, table is Sheet1);

data:
NOCONCATENATE LOAD * RESIDENT tmpdata WHERE IsNUm(WEIGHTAGE);

DROP TABLE tmpdata;

[shekhar_analyti]

Hi Gysbert ,

Thanks for the reply .

With you solution i am getting below result ,

BUT expected was 

[shekhar_analyti]

@sunny_talwar 

Hi Sunny Bhai .. Please help !!

[Gysbert_Wassenaar · Accepted solution]

Try this then:

tmpdata:
LOAD
*,
If(Len(Trim(NAME))=0 AND (Len(Trim(Previous(NAME)))>0 OR RowNo()=1), ID, Peek(TEAM)) as TEAM,
If(Len(Trim(NAME))=0 AND Len(Trim(Previous(NAME)))=0 AND RowNo()>1, ID, Peek(TYPE)) as TYPE
;
LOAD
A as ID,
B as NAME,
C as REGION,
D as WEIGHTAGE
FROM
[D:\temp\c1546407 - EXCEL HANDALING.xlsx]
(ooxml, no labels, table is Sheet1)
WHERE
A <> 'ID'
;


data:
NOCONCATENATE LOAD * RESIDENT tmpdata WHERE IsNUm(WEIGHTAGE);

DROP TABLE tmpdata;