topic Re: Crosstable numeric values in QlikView

Crosstable numeric values

Tue, 02 Oct 2012 15:05:34 GMT

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

Re: Crosstable numeric values

whiteline — Tue, 02 Oct 2012 15:37:15 GMT

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.

Re: Crosstable numeric values

Tue, 02 Oct 2012 16:46:51 GMT

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.

Re: Crosstable numeric values

whiteline — Tue, 02 Oct 2012 16:51:22 GMT

Hi.

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

Re: Crosstable numeric values

Thu, 04 Oct 2012 11:24:49 GMT

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

Miguel_Angel_Baeyens — Thu, 04 Oct 2012 11:58:46 GMT

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

Re: Crosstable numeric values

Thu, 04 Oct 2012 13:42:29 GMT

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

Miguel_Angel_Baeyens — Thu, 04 Oct 2012 15:04:46 GMT

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

Re: Crosstable numeric values

Anonymous — Thu, 04 Oct 2012 15:08:58 GMT

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

));

Re: Crosstable numeric values

Thu, 04 Oct 2012 15:14:57 GMT

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

Re: Crosstable numeric values

Miguel_Angel_Baeyens — Thu, 04 Oct 2012 16:31:38 GMT

Hi,

You cannot do that in the same load statement, therefore the two loads I sampled above. Irrespective of what functions do you use, the Crosstable() transforms the field name (even if you are using a Date() function) into a string literal, that stores as a string in the value, so you need to transform it again.

Hope that makes sense.

Miguel

Re: Crosstable numeric values

Mon, 08 Oct 2012 13:18:32 GMT

Hey,

after implementing the code provided my script looks way nicer and runs more smoothly

Still my problem remains, which makes me even more desperate because I have no clue where the error occurs from. I will try to describe my problem in better....:

These are the tables I am currently working on.

The SalesData is supposed to be be in the table "Data" (which is a crosstable)

The table contains of several Excelsheets which are formatted in the same way only different/more/less "Debitor" (engl. debtor) Values. One sheet contains the data for one month. The script for the table looks like this:

Directory;
Datatemp:
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))
));
STORE Datatemp into Temp.qvd (qvd);
DROP Table Datatemp;
Data:
LOAD Zeile,
Debitor,
Menge
FROM Temp.qvd (qvd);

I execute this script for every month (with different debtor(Debitor) values for every month).

Now QlikView somehow "looses" the Data which results in a table "Data" containing the following:

As you can see, the value for "Debitor" (engl. debtor) is the same as "Menge" (engl. sum).

My goal is to have the actual data in the column "Menge".

Thank you very much for the answers already provided, they helped a lot improving my understanding of QV

Re: Crosstable numeric values

Miguel_Angel_Baeyens — Mon, 08 Oct 2012 14:10:27 GMT

Hi,

Check the ammeded script, so the Debitor is always numeric after the crosstable:

Directory;
Datatemp:
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))
));
STORE Datatemp into Temp.qvd (qvd);
DROP Table Datatemp;
Data:
LOAD Zeile,
Num(Debitor) AS Debitor, // This is the new line
Menge
FROM Temp.qvd (qvd);

Hope that helps.

Miguel

Re: Crosstable numeric values

Mon, 08 Oct 2012 14:28:27 GMT

Only slow but steady we are getting ahead

Now he seems to establish a correct relationship between the deptors. Still the value under "Menge" is the same number as the value for Debtor....it looks like this now:

Thus the old problem with my missing values remain. When I loaded the script with "CustomerXYZ" instead of the numeric customerID (Debitor) it worked fine. Only were several typos in the customer name which resulted in a loss of data.

Re: Crosstable numeric values

Anonymous — Tue, 09 Oct 2012 07:31:26 GMT

Hi,

Again, not sure whether this targets your specific issue.

I had a problem with loading in a cross table where the column names, instead of being numeric, were strings that were too long and complex to test against and manipulate at later stages. They were the questions, options and answers from a survey monkey data dump.

I opted for not using embedded labels, but instead using "F" followed by the column number (F1...Fx) labels. I then set up a spreadsheet with the column headers, which I loaded in to a mapping table. I then performed an applymap() to replace the question and option labels, based upon the column position.

Though my mapping spreadsheet was static, I could presumably have created it from the header rows of a dynamic source spreadsheet.

Hopefully this makes sense and, if you're still struggling, gives a potential change of direction.

Jonathan

Re: Crosstable numeric values

Tue, 09 Oct 2012 15:39:07 GMT

Unfortunately that doesnt help. I need to identfy the columns properly. The amount and content change from month to month. Without the headers I am not able to identify my data correctly.

Thank you very much anyway. Any advice could help me.

Re: Crosstable numeric values

Tue, 16 Oct 2012 07:13:49 GMT

No new ideas how to solve this problem?

Re: Crosstable numeric values

whiteline — Tue, 16 Oct 2012 18:59:50 GMT

Hi.

In Miguel scrip correct:

Num#(Debitor) AS Debitor

Re: Crosstable numeric values

Wed, 17 Oct 2012 14:31:41 GMT

hahaha....i really though I would have tried that already, apparently I didn't

And of course it does solve my initial question.

Thanks especially to whiteline and Miguel.

Still something about this bugs me. The preview displays the wrong values again.

But I will think about that myself first

You really helped me a lot.