Crosstable numeric values

[#1]

Hey,

I try to load an Excel Sheet using this Script.

Unfortunately QV does not recognize the numeric rows appropriately. Tried to format them as text(...) but didnt help.

The numbers are CustomerIDs on which sales data (Menge) is aggregated.

I believe QV thinks they are the values in the table and not an ID.

Directory;

CrossTable(Debitor, Menge, 2)

LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,

     Zeile,

     10005,

     10104,

      ...........

     .................

      ...........

     90007,

     99999

FROM

KOST\201207.xls

(biff, embedded labels, table is Tabelle3$, filters(

Remove(Col, Pos(Top, 3))

));

The same version worked with the customers actual name as ID instead of the numeric.

Any Ideas? Or need additional information?

Thx Thees

[whiteline]

QV does not recognize the numeric rows appropriately

Hi.

What do you mean ?

I think it successfully creates a table with four fields:  CalendarMonthAndYear, Zeile, Debitor, Menge.

[#3]

The table created is referenced to two other tables over the attributes "CalendarMonthAndYear" and "Debitor".

In the data preview in the table-modell (the ctrl + t stuff) QV lists the "Debitor" Values under "Menge".

"Menge" should contain the sales.

When I create a Listbox with "Debitor" he lists all the Debitors twice. First those from the upper table, and Second those from the corresponding table (which relates the Debitors to a sales person).

In the Listbox some are aligned right and some left. This is why I concluded there is a problem with the numeric format. Which is why he doesnt connect the two tables properly.

[whiteline]

Hi.

Ok. Crosstable() statement always handles  headers as text. To transform your Debitor values to numbers use one more LOAD statement and num#() function.

[#5]

Sry for late response aaaand thank you for your help

How does such a LOAD statement need to look like?

Do you mean an INLINE LOAD?

Unfortunately I cannot test this before tuesday next week but I definetly appreciate your help.

[Miguel_Angel_Baeyens]

Hi,

As whiteline points, all field names converted to values in the Crosstable() load will be stored as literals of texts, instead of numbers. Dates in QlikView are numeric, so you need a second load to transform the values correctly. According to the following sample:

DataTemp:
CROSSTABLE (Month, Value) LOAD ID,
     January AS "01/01/2012",
     February AS "01/02/2012",
     March AS "01/03/2012"
FROM File.xls (...);
STORE DataTemp INTO Temp.qvd (qvd);
DROP TABLE DataTemp;
Data:
LOAD ID,
     Date(Date#(Month, 'DD/MM/YYYY')) AS ActualMonthDate,
     Value
FROM Temp.qvd (qvd);

The first step is always the Crosstable() load, the second reads the same table but transforms the values into actual numeric values. Using QVD files you are likely to save time in the load process.

Hope that helps.

Miguel

[#7]

Thank your very much. I will try a solution based on the given example.

Next tuesday i'll tell you wether it was successfull or if I am crying

Besides that, why does everyone always create temporary tables?

Are there any Whitepapers or Blog-Reports on this topic? I am relatively new to QV and still need to figure out a lot of stuff

[Miguel_Angel_Baeyens]

Hi,

They are not real temporary tables, rather the steps you need to follow in order to get the information clean and ready to use in the charts. Obviously, the less steps to take, the faster it will go, but usually the more complex it will be as well...

Hope that helps.

Miguel

[Anonymous]

Hi,

Not sure if this has already been dealt with, but I hit this problem yesterday. I had to put square brackets around the Column headers, like so:

CrossTable(Debitor, Menge, 2)

LOAD date(date#(Datum, 'MM/YYYY'), 'MMYYYY') as CalendarMonthAndYear,

     Zeile,

     [10005],                // see these square brackets

     [10104],

      ...........

     .................

      ...........

     [90007],

     [99999]

FROM

KOST\201207.xls

(biff, embedded labels, table is Tabelle3$, filters(

Remove(Col, Pos(Top, 3))

));

[#10]

@jonbroughavone: I read about that solution as well, but in a test it didn't work out well. Additionally the amount of columns I would have to adjust is too large to do manually.