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

[Miguel_Angel_Baeyens]

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

[#3]

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

[Miguel_Angel_Baeyens]

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

[#5]

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.

[Anonymous]

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

[#7]

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.

[#8]

No new ideas how to solve this problem?

[whiteline · Accepted solution]

Hi.

In Miguel scrip correct:

Num#(Debitor) AS Debitor

[#10]

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.