Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

shekhar_analyti
Valued Contributor

How to format given unstructured excel data ?

Hi All ,

How to format below unstructured excel data ?

P.png

 

INPUT 

IDNAMEREGIONWEIGHTAGE
SALES
ID1NAME1R10.44074642
ID2NAME2R20.10343234
ID3NAME3R30.4314422
ID4NAME4R40.45019801
ID5NAME5R50.37139072
ID6NAME6R60.81501561
ID7NAME7R70.55574406
ID8NAME8R80.55555429
PR
IDNAMEREGIONWEIGHTAGE
ADHOCS
ID9NAME9R90.23475478
ID10NAME10R100.54411475
ID11NAME11R110.58992939
ID12NAME12R120.04174845
LEGAL
IDNAMEREGIONWEIGHTAGE
TEMP
ID13NAME13R130.06142142
ID14NAME14R140.79227561
ID15NAME15R150.90962549

 

OUTPUT 


IDNAMEREGIONWEIGHTAGETEAMTYPE
ID1NAME1R10.130993109SALES 
ID2NAME2R20.661050116SALES 
ID3NAME3R30.972663368SALES 
ID4NAME4R40.413523484SALES 
ID5NAME5R50.110618327SALES 
ID6NAME6R60.413701376SALES 
ID7NAME7R70.503030479SALES 
ID8NAME8R80.476772841SALES 
ID9NAME9R90.44600587PRADHOCS
ID10NAME10R100.274699848PRADHOCS
ID11NAME11R110.905506174PRADHOCS
ID12NAME12R120.739754741PRADHOCS
ID13NAME13R130.4320139LEGALTEMP
ID14NAME14R140.962760651LEGALTEMP
ID15NAME15R150.711687606LEGALTEMP

 

Thanks & Regards

Shekar

 

 

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to format given unstructured excel data ?

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;




talk is cheap, supply exceeds demand
4 Replies
MVP & Luminary
MVP & Luminary

Re: How to format given unstructured excel data ?

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;


talk is cheap, supply exceeds demand
shekhar_analyti
Valued Contributor

Re: How to format given unstructured excel data ?

Hi Gysbert ,

Thanks for the reply .

With you solution i am getting below result ,

P.png

 

BUT expected was 

 

2.png

shekhar_analyti
Valued Contributor

Re: How to format given unstructured excel data ?

@sunny_talwar 

Hi Sunny Bhai .. Please help !!

MVP & Luminary
MVP & Luminary

Re: How to format given unstructured excel data ?

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;




talk is cheap, supply exceeds demand