topic Transform Table in QlikView

Transform Table

Wed, 17 Mar 2010 17:30:51 GMT

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

Transform Table

Wed, 17 Mar 2010 18:08:54 GMT

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

Transform Table

Wed, 17 Mar 2010 18:15:10 GMT

Transform Table

Wed, 17 Mar 2010 18:18:50 GMT

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

Transform Table

Wed, 17 Mar 2010 21:28:14 GMT

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*;

Transform Table

Thu, 18 Mar 2010 09:34:29 GMT

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