In a script few changes are require...

[pra_kale]

Hi,

Below given is the script which is importing the sheets into Qlik application.

I am facing below given challenges in below given script

• I am not getting months in a Columns, as per the sheets attached....( '$(v.SHEET) !' & %Col as %Col as far as my knowledge this statement from the code is not working properly so unable to find join.)
• Where ever value is in % i am getting value in a 0.12 or 0.47 format where as I want value to be get displayed as is means 2% or 3.54%.

I am attaching below application as well as excel file on which I am working.

Can you please help to resolve the Issue.

For Each v.SHEET in

'BANK','Direct','Alternate','Agency'

[RAWSHEET]:

LOAD

*

From

(ooxml, no labels, table is $(v.SHEET));

v.Cols = NoOfFields('RAWSHEET') +2;

[COLHEADERS]:

LOAD

'$(v.SHEET) !' and

if(RowNo()>= 25,'A' & Chr( Ord('A')-26 + IterNo()), chr( Ord('B') + IterNo() )) as %Col,

if(RowNo()>= 25, Peek('A' & chr

(Ord('A')-26 + IterNo()),0,'RAWSHEET'), Peek(chr(Ord('B') + IterNo())

,0,'RAWSHEET')) as Date,

if(RowNo()>= 25, Peek('A' & chr

(Ord('A')-26 + IterNo()),0,'RAWSHEET'), Peek(chr(Ord('B') + IterNo())

,1,'RAWSHEET')) as ActualOrTarget,

$(v.Cols) as Col

While

    IterNo() < $(v.Cols);

LOAD * Resident [RAWSHEET] Where RecNo()=1;

[WORKSHEET_CROSSTABLE]:

CrossTable(%Col,Data,5)

LOAD

*

Resident

    [RAWSHEET]

Where

RecNo()>2;

[worksheets]:

LOAD

    '$(v.SHEET)' as Channel,

    '$(v.SHEET) !' & %Col as %Col,

    A as Metric,

    B as UOM,

    C as KPI,

    Num# (Data) as Val

Resident

    [WORKSHEET_CROSSTABLE];

    DROP Table [WORKSHEET_CROSSTABLE];

    DROP Table [RAWSHEET];

NEXT;

v.SHEET = Null();

v.Cols = Null();

EXIT Script        

[settu_periasamy]

Hi,

Try the below script. My changes are in Blue color

Directory;

For Each v.SHEET in 'BANK','Direct','Alternate','Agency'

[RAWSHEET]:

LOAD

*

From

Channel_Performace.xlsx

(ooxml, no labels, table is $(v.SHEET));

v.Cols = NoOfFields('RAWSHEET') +2;

[COLHEADERS]:

LOAD

//To Generate As Many Rows As There are columns

if(RowNo()>= 25,'A' & Chr( Ord('A')-26 + IterNo()), '$(v.SHEET) !' & chr( Ord('B') + IterNo() )) as %Col,

if(RowNo()>= 25, Peek('A' & chr (Ord('A')-26 + IterNo()),0,'RAWSHEET'), Peek(chr(Ord('B') + IterNo())

,0,'RAWSHEET')) as Date,

if(RowNo()>= 25, Peek('A' & chr (Ord('A')-26 + IterNo()),0,'RAWSHEET'), Peek(chr(Ord('B') + IterNo())

,1,'RAWSHEET')) as ActualOrTarget,

$(v.Cols) as Col

While

    IterNo() < $(v.Cols);

LOAD * Resident [RAWSHEET] Where RecNo()=1;

[WORKSHEET_CROSSTABLE]:

CrossTable(%Col,Data,5)

LOAD

*

Resident    [RAWSHEET]

   Where

RecNo()>2;

[worksheets]:

LOAD

    '$(v.SHEET)' as Channel,

    '$(v.SHEET) !' & %Col as %Col,

    A as Metric,

    B as UOM,

    C as KPI,

    if(WildMatch(B,'%'),Num(Data,'#,##0.00%'),Num# (Data)) as Val  

Resident

    [WORKSHEET_CROSSTABLE];  

    DROP Table [WORKSHEET_CROSSTABLE];

    DROP Table [RAWSHEET];

NEXT;

v.SHEET = Null();

v.Cols = Null();

EXIT Script ;

[pra_kale]

Thanks for your Reply...

I have tried with the above code..below given things which I have noticed

1) Total Target, Total Actual and Mar-16 columns are not get imported...

2) The Dates columns are visible only if I select Bank Tab, but If I select other Tabs then I am not getting Dates.

3) Where ever data is in %, is still not get captured in % format...it is still showing as 0.02, 0.04..

Can you please help in this as well.

Thanks in advance.

[rwunderlich]

I'm not sure what you want your final data model to look like, but a simpler script to deal with the difficult layout of the excel sheets may be:

For Each v.SHEET in

'BANK','Direct','Alternate','Agency'

Data:

CrossTable(Month, Value, 6)

LOAD '$(v.SHEET)' as Channel, *

FROM

Channel_Performace.xlsx

(ooxml, embedded labels, table is [$(v.SHEET)], filters(

Replace(1, bottom, StrCnd(null)),

Replace(2, bottom, StrCnd(null)),

Replace(3, bottom, StrCnd(null))

))

WHERE RecNo() <> 2

;

NEXT v.SHEET

And then you can clean it up and transform it like:

Data2:

// Relying on the fact that the second occurrence of Apr-15 will

// get named Apr-151 by the crosstable load.

LOAD

  *,

  Date(Date#(left(Month,6), 'MMM-YY'), 'MMM-YY') as Date,

  if(len(Month)>6, 'Actual', 'Target') as TargetOrActual

Resident Data

;

DROP TABLE Data;

DROP FIELD Month;

Example attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[pra_kale]

Thanks for your help...

I have make the changes which you have suggested and executed the code which you have given. But what what I have noticed is

1) NOP, Total Target, Total Actual columns are not get imported in a Table.

2) I want % column should get imported in % format only like...3%,4% etc..and not like 0.03, 0.04

Other parameters are get's imported properly...for reference I am attaching the excel file which I am trying to import.

Below given is the script which I have used..can you please suggest what changes I should make in the below script by which above mentioned issues can get resolve.

Thanks in Advance..

For Each v.SHEET in

'BANK','Direct','Alternate','Agency'

Data:

CrossTable(Month, Value, 6)

LOAD '$(v.SHEET)' as Channel, *

FROM

(ooxml, embedded labels, table is [$(v.SHEET)], filters(

Replace(1, bottom, StrCnd(null)),

Replace(2, bottom, StrCnd(null)),

Replace(3, bottom, StrCnd(null)),

))

WHERE RecNo() <> 2

;

NEXT v.SHEET

Data2:

// Relying on the fact that the second occurrence of Apr-15 will

// get named Apr-151 by the crosstable load.

LOAD

  *,

  Date(Date#(left(Month,6), 'MMM-YY'), 'MMM-YY') as Date,

  if(len(Month)>6, 'Actual', 'Target') as TargetOrActual

Resident Data

;

DROP TABLE Data;

DROP FIELD Month;

EXIT Script

[rwunderlich]

Please read up on the CrossTable statement prefix in  the Help and Ref Guide. You'll see that the "6" in this example, indicates how many columns to skip before loading. If modify that number you can include the Total columns.

CrossTable(Month, Value, 6)

With regards to the % values, I think you would need to use the text() function in your load to include the % as string. You can't use text() with "*" column list, so you would have to list every column.

As an alternative, format the data later when you display it like.

num(sum(Value), if(UOM='%', '0.00%', '#,##0')

-Rob