topic Re: Excel data stored as text in QlikView

Excel data stored as text

Wed, 22 May 2013 12:12:05 GMT

Hi All,

I used QV to display company fuel card data, by loading in monthly excel data files. Unfortunately the fuel card company has changed (but we have kept the same 16 digit long fuel card numbers) & so the excel report format has also changed.

The '2012 company' reported 16 digit fuel card numbers as 'general' cell format within excel, but the value had a space before it to ensure excel reported the entire 16 digit value.

The new '2103 company' report the 16 digit fuel card number as 'text' cell format within excel, with no space before the 16 digit value.

When i load this into QV (by concatenating the 1st file) it is displaying two different fuel card numbers, for example '1234567812345678' and ' 1234567812354678'.

Is there a simple way to tell QV that these should load in as the same, i.e. how can i get it to add a space before the '2013 company's' text value?

Many Thanks

Re: Excel data stored as text

Gysbert_Wassenaar — Wed, 22 May 2013 12:40:57 GMT

' ' & ColumnX as cardnumber

An alternative is to strip the preceding space by using the trim function in both loads:

trim(ColumnX) as cardnumber

Re: Excel data stored as text

Wed, 22 May 2013 13:18:37 GMT

Thanks for your response, i'm having trouble getting the new text into the correct part of the script....

This is my first loaded file script:


April840416_2012:
LOAD [Account number], 
     [Registration number], 
     [Driver name], 
     [Card number], 
     [Gross amount], 
     [Unit price], 
     Quantity, 
     [Product Charge description], 
     [Transaction date], 
     [Transaction mileage amount], 
     [Brand name],
     Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year'
FROM
[2012-13 car downloads\840416_Apr_12.xlsx]
(ooxml, embedded labels, header is 5 lines, table is [626717_report120531112103])

and this is the new data file:


JanToMar840416_2013:
CONCATENATE (April840416_2012) LOAD *,
 Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year'
FROM
[2012-13 car downloads\840416_Jan_Mar_13.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE [Product Charge description] <>'Card Fee';

Ideally i'd like to use " ' ' & ColumnX as cardnumber" but it didn't work when i tried adding the following to the new 2013 data:


JanToMar840416_2013:
CONCATENATE (April840416_2012) LOAD *,
 Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year',
     ' ' & [Card number] as [Card number]
FROM
[2012-13 car downloads\840416_Jan_Mar_13.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE [Product Charge description] <>'Card Fee';

Where should the text go?

Re: Excel data stored as text

Gysbert_Wassenaar — Wed, 22 May 2013 13:26:41 GMT

It should go instead of the *. The * means load all fields so you already have a field named [Card number]. Trying to add it again will result in an error message. You'll have to list all the fields like you did for the 2012 load.

Re: Excel data stored as text

Wed, 22 May 2013 14:16:10 GMT

Hi Gysbert, i've amended the 2013 data script to:

JanToMar840416_2013:
LOAD [Account number], 
     [Card Number], 
     [Driver name], 
     [Registration number], 
     [Transaction date], 
     [Transaction mileage amount], 
     [Product Charge description], 
     Quantity, 
     [Unit price], 
     [Gross amount], 
     [Brand name], 
     ' ' & [Card Number] as [Card number],
     Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year'
FROM
[2012-13 car downloads\840416_Jan_Mar_13.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 3))));

However the card number still shows as having a space in the 2013 data:

And by not concatenating my file now contains a synthetic table which i've been trying to avoid?

Re: Excel data stored as text

Thu, 23 May 2013 16:05:43 GMT

try with the same number of fields - it should avoid the synthetic key at least. Also I would personally go for the trim rather than the pad approach as Gysbert suggested.

April840416_2012:
LOAD [Account number],
trim(   [Card number]) as [Card number],
[Driver name],
       [Registration number],
   [Transaction date],
     [Transaction mileage amount],
     [Product Charge description],
     Quantity,
     [Unit price],
    [Gross amount],
   [Brand name],
     Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year'
FROM
[2012-13 car downloads\840416_Apr_12.xlsx]
(ooxml, embedded labels, header is 5 lines, table is [626717_report120531112103]);

concatenate

LOAD [Account number],
     [Driver name],
     [Registration number],
     [Transaction date],
     [Transaction mileage amount],
     [Product Charge description],
     Quantity,
     [Unit price],
     [Gross amount],
     [Brand name],
    trim( [Card Number] ) as [Card number],
     Year ([Transaction date]) as Year,
     Month ([Transaction date]) as Month,
     yearname([Transaction date],0,4) as 'Financial Year'
FROM
[2012-13 car downloads\840416_Jan_Mar_13.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Col, Pos(Top, 3))));

Re: Excel data stored as text

Gysbert_Wassenaar — Thu, 23 May 2013 17:05:45 GMT

i.e. how can i get it to add a space before the '2013 company's' text value?

You ask...

However the card number still shows as having a space in the 2013 data:

And you get what you asked for.

Problem solved

If you changed your mind and don't want spaces then don't add them in the first place and remove them if they are there already with the trim function,

Re: Excel data stored as text

Fri, 24 May 2013 15:55:37 GMT

Thanks. I used the trim function on all 2012 data - renamed to match the new 2013 data, which then allowed me to concatenate in 2013 data & avoid synthetic tables