Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Crosstable numeric values

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

Tags (2)
1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Crosstable numeric values

Hi.

In Miguel scrip correct:

Num#(Debitor) AS Debitor

18 Replies
whiteline
Honored Contributor II

Re: Crosstable numeric values

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.

Not applicable

Re: Crosstable numeric values

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
Honored Contributor II

Re: Crosstable numeric values

Hi.

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

Not applicable

Re: Crosstable numeric values

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.

Re: Crosstable numeric values

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

Not applicable

Re: Crosstable numeric values

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

Re: Crosstable numeric values

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

jonbrough
Valued Contributor

Re: Crosstable numeric values

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))

));

Not applicable

Re: Crosstable numeric values

@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.

Community Browser