Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excel import, bit more complicated

Hello,

I have attached example file (Budget_exampledata) where sales person have added budget for year 2015 per month (B2015) which I would like to import. The question is now that that is has excess data and also normal cross table doesn't fit as it has two rows as qualifiers or how to say..

First rows as example:

MonthNum112233445566778899101011111212
ProductSalespersonZoneTOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015
FREEZERKIAAPE777E54E0E0E0E00E07540E0003210432

And the result should be (colors are just to give the hint what data is what):

ProductSalespersonZoneDatatypeDateAmount
FREEZERKIAAPBudget1.1.2015777
FREEZERKIAAPBudget1.2.201554
FREEZERKIAAPBudget1.8.2015754
FREEZERKIAAPBudget1.11.2015321
FREEZERKIAAPBudget1.12.2015432

So the TOT2014 rows are not needed. As they are in the file just to give the hint for sales person for his totals previous years. Also the year (in yeallow) is not mandatory to get from the file, that can be done manually.

-Juha

1 Solution

Accepted Solutions
Not applicable
Author

Hi Juha,

Interesting load! What I would suggest is a cross table to initially and then a resident load to apply the new field and also handle the final formatting. I have tested the below script on your sample data and it works.

A

InitialLoad:

CrossTable(Month, Data, 3)

LOAD F1 as Product,

     F2 as Salesperson,

     MonthNum,

     [11] as [01/01/2015],

     [21] as [01/02/2015],

     [31] as [01/03/2015],

     [41] as [01/04/2015],

     [51] as [01/05/2015],

     [61] as [01/06/2015],

     [71] as [01/07/2015],

     [81] as [01/08/2015],

     [91] as [01/09/2015],

     [101] as [01/10/2015],

     [112] as [01/11/2015],

     [121] as [01/12/2015]

FROM

[..\Downloads\Budget_exampledata.xlsx]

(ooxml, embedded labels, table is Budget);

FormattedTable:

LOAD

  Product,

  Salesperson,

  MonthNum AS Zone,

  'Budget' AS Datatype,

  Month,

  Data AS Amount

Resident InitialLoad

Where Product<>'Product'

;

Drop Table InitialLoad;

View solution in original post

2 Replies
Not applicable
Author

Hi Juha,

Interesting load! What I would suggest is a cross table to initially and then a resident load to apply the new field and also handle the final formatting. I have tested the below script on your sample data and it works.

A

InitialLoad:

CrossTable(Month, Data, 3)

LOAD F1 as Product,

     F2 as Salesperson,

     MonthNum,

     [11] as [01/01/2015],

     [21] as [01/02/2015],

     [31] as [01/03/2015],

     [41] as [01/04/2015],

     [51] as [01/05/2015],

     [61] as [01/06/2015],

     [71] as [01/07/2015],

     [81] as [01/08/2015],

     [91] as [01/09/2015],

     [101] as [01/10/2015],

     [112] as [01/11/2015],

     [121] as [01/12/2015]

FROM

[..\Downloads\Budget_exampledata.xlsx]

(ooxml, embedded labels, table is Budget);

FormattedTable:

LOAD

  Product,

  Salesperson,

  MonthNum AS Zone,

  'Budget' AS Datatype,

  Month,

  Data AS Amount

Resident InitialLoad

Where Product<>'Product'

;

Drop Table InitialLoad;

MarcoWedel

Hi,

one slightly more generic approach without hard coded field names could be:

QlikCommunity_Thread_144881_Pic1.jpg

tabSalesBudget:

CrossTable(ColNam, Amount, 3)

LOAD *

FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]

(ooxml, no labels, header is 2 lines, table is Budget, filters(Transpose(),Transpose()))

Where RecNo()<=16;

mapColNam:

mapping LOAD

  '@'&(RecNo()+3),

  MakeDate(Right(@2,4),@1)

FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]

(ooxml, no labels, table is Budget, filters(Transpose(),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))

Where @2 like 'B*';

Right Join (tabSalesBudget)

LOAD Distinct

  ColNam,

  Date(ApplyMap('mapColNam', ColNam)) as Date

Resident tabSalesBudget

Where ApplyMap('mapColNam', ColNam);

DROP Field ColNam;

tabTemp:

CrossTable(ColNum, ColNam2)

LOAD 1, *

FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]

(ooxml, no labels, header is 1 lines, table is Budget, filters(Transpose(),Transpose()))

Where RecNo()=1;

mapColNam2:

Mapping LOAD ColNum, ColNam2 Resident tabTemp;

DROP Table tabTemp;

RENAME Fields using mapColNam2;

Hope this helps

Regards

Marco