Transform Table

[#1]

Hi,

Apologies for the very bad format of this post. Nonetheless, I have been struggling in applying the [Code] and[ /code] trick suggested by Jason in his post.

I have the following table in Excel loaded QV.

QUALIFY*;

UNQUALIFY ElectricalDevicesDateID;

ElectricalDevices:

LOAD

autonumber(UpdatedDate) AS ElectricalDevicesDateID,

UpdatedDate,

PDA,

Blackberry,

Phone,

Laptop

FROM

.. \INFOSEC\InfosecKPIs.xls

(biff, embedded labels, table is Sheet2$);

UNQUALIFY*;

I would like to load the table into QV so that I get to the result below:

UpdatedDate Type Quantity

31/1/2010 Laptop 10

31/1/2010 PDA 3

31/1/2010 Balckberry 1

31/1/2010 Phone 10

28/2/2010 PDA 5

28/2/2010 Phone 7

And so forth and so on.

In other words, I have a crosstab in Excel with fields as Laptop, PDA, Phone and their values in the cells by date.

Any help would be much appreciate.

Andrea

[#2]

Friend, try this:

ElectricalDevices:
LOAD
autonumber(UpdatedDate) AS ElectricalDevicesDateID,
UpdatedDate,
PDA,
Blackberry,
Phone,
Laptop
FROM ..\INFOSEC\InfosecKPIs.xls (biff, embedded labels, table is Sheet2$);

Final:
Load
UpdatedDate,
count(Blackberry) as Quantity,
'Blackberry' as Type
resident ElectricalDevices
group by UpdatedDate;

concatenate
Load
UpdatedDate,
count(PDA) as Quantity,
'PDA' as Type
resident ElectricalDevices
group by UpdatedDate;

concatenate
Load
UpdatedDate,
count(Phone) as Quantity,
'Phone' as Type
resident ElectricalDevices
group by UpdatedDate;

concatenate
Load
UpdatedDate,
count(Laptop) as Quantity,
'Laptop' as Type
resident ElectricalDevices
group by UpdatedDate;

Regards Luciano

[#3]

[#4]

I have problems with the format of my answer. If you dont understand, send me an email, and I will send you the solution.

Mail: luciano.straface@comafi.com.ar

Regards

Straface Luciano

[#5 · Accepted solution]

thats an easy way with crosstable .. ;

QUALIFY*;
UNQUALIFY ElectricalDevicesDateID;
crosstable (Type,Quantity,2)LOAD autonumber(UpdatedDate) as ElectricalDevicesDateID,
 UpdatedDate,
 PDA,
 Blackberry,
 Phone,
Laptop
FROM
..\INFOSEC\InfosecKPIs.xls
(biff, embedded labels, table is Sheet2$);
UNQUALIFY*;

[#6]

Straface,

I have taken into account your answer that I will certainly used in a similar script. Nonetheless, I have found Onder's script more practicle and compaq. Thank you both very much for your support. I hope to be able to reciprocate uin the future.

Andrea