topic Re: Excel Sheet has unstructured Data in QlikView

Excel Sheet has unstructured Data

mohitd910 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Excel Sheet has unstructured Data

nagarjuna_kotha — Sun, 09 Oct 2016 09:55:51 GMT

what is ur expected output ?

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

Advanced Cross Table

-Nagarjun

Re: Excel Sheet has unstructured Data

Anil_Babu_Samineni — Sun, 09 Oct 2016 10:11:20 GMT

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,

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

Re: Excel Sheet has unstructured Data

mohitd910 — Sun, 09 Oct 2016 12:43:31 GMT

I dont want this bro

Re: Excel Sheet has unstructured Data

nagarjuna_kotha — Sun, 09 Oct 2016 12:49:36 GMT

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

-Nagarjun

Re: Excel Sheet has unstructured Data

jagan — Mon, 10 Oct 2016 02:51:00 GMT

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,

FROM

[ABC.xlsx]

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