Excel Sheet has unstructured Data

[mohitd910]

Dear All,

I have an excel sheet. In my excel sheet data is unstructured. The Headers are in b/w the excel sheet. How i can use this excel sheet.

Manish Kachhia

stalwar1‌

jagan‌

Mayil Vahanan Ramasamy

Regards,

Mohit Dahiya

[nagarjuna_kotha]

what is ur expected output ?

Recently i have created one blog,check that, u ll get some idea.

Advanced Cross Table

-Nagarjun

[Anil_Babu_Samineni]

Mohit, By using Cross Table, Without cross Table i don't think if we can achieve that.

CrossTable(MonthQuarter, Data, 3)

LOAD Num(#,'0.0') as #,

    Text(Indicator) as Indicator,

    Num(Target%,'00') as Target%,

    Jan,

    Feb,

    Mar,

    Q1,

    Apr,

    May,

    Jun,

    Q2,

    Jul,

    Aug,

    Sep,

    Q3,

    Oct,

    Nov,

    Dec,

    Q4

FROM

ABC.xlsx

(ooxml, embedded labels, table is Sheet2, filters(

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20))

));

And then, Try from Partial Sums from presentation tab of your Pivot Report. Then, Create two more fields which are

Average 2015

Focal Point

For Ref, PFA. I think so you are using Qliksense. I don't have S/W of Qliksense. Hope helps you

[mohitd910]

I dont want this bro

[nagarjuna_kotha]

try to share output of ur file, then only we can help  you ....

-Nagarjun

[jagan]

Hi,

Try this, I didn't done the CrossTable, crosscheck the script and later do the CrossTable.

LOAD

*

WHERE Len(Trim(SNo)) > 0;

LOAD If(Len(Trim(#)) = 0, Num(Peek(SNo),'0.0'), Num(#,'0.0')) as SNo,

     If(Len(Trim(Indicator)) = 0, Peek(Indicator_New), Text(Indicator)) as Indicator_New,

     If(Len(Trim(Target%)) = 0, Num(Peek(Target%_New),'00'),  Num(Target%,'00')) as Target%_New,

     Jan,

     Feb,

     Mar,

     Q1,

     Apr,

     May,

     Jun,

     Q2,

     Jul,

     Aug,

     Sep,

     Q3,

     Oct,

     Nov,

     Dec,

     Q4

FROM

[ABC.xlsx]

(ooxml, embedded labels, header is 2 lines, table is Sheet1);