Row values as fields (opposite of crosstable?)

[#1]

Hey guys,

I'm doing a little more QV development these days (getting my feet wet with basic data modeling, etc.)

I have a table like this:

Field1	Attribute	Value
A	xx	1034
A	yy	4076
A	zz	34
B	xx	2345
B	yy	2435

where the attributes are actually field names...my desired table looks like this:

Field 1	xx	yy	zz
A	1034	4076	34
B	2345	2435	--

If you could even provide the name of the script function required to pull this off, that would be a big help. Thanks!

[#2]

hi

see thee .qvw application

thanks

zaman

[shumailh]

Jason,

I think you can do it using create chart tool (Chart Type : Pivot Table) by dragging attribute values columnwise.

OR

You want the actual table to be look like this? if it's then we can use cross table for unique values but here we have dublicate values in attribute field, so we have to do it in multiple steps using qvd i guess.

Shumail

[#4]

Thanks for the effort guys!

I apologize for not being clearer; this isn't a presentation issue, this is a data issue. I want to pull out these "fields" from rows of the data to create listboxes for filtering of data.

Anyone else?

[vgutkovsky]

Jason,

If it wasn't for Field1, I would say that a simple Right rotation would do the trick. But Field1 makes things more complicated. So I would say that the best approach would be to create the pivot table in Excel (see attached) and then do a straight import into QlikView with the following syntax:

mytable:
LOAD Field1,
 xx,
 yy,
 zz
FROM
test1.xls
(biff, embedded labels, table is Sheet2$, filters(
Remove(Row, Pos(Top, 1)), //This is the pivot table header row
Remove(Row, Pos(Top, 4)) //This is the "Grand Total" row
));

I didn't do much cleanup on the pivot table, and instead just removed the first and last rows; maybe you can come up with a more elegant solution. But hopefully this will give you a good starting point.

Cheers,

[#6]

I've expereinced the exact same data storage model and requried transformation with a WFM system and how it stored the attributes of each worker, i used a loop to build a flat table from the transactional entries.

First load all three fieldsyou specified above into a table called "datagroup" then use this script:

//import field list and count number of fields
masterfields:
LOAD distinct Attribute
resident datagroup;
//count no of distinct attributes
let vfieldnos# = fieldvaluecount('Attribute');
//create pk list
fields:
load distinct Field1
resident datagroup;
//set loop to zero
let counter#=0;
//loop through every attribute
for counter#=0 to ($(vfieldnos#)-1)
//createfield names
let vfield = peek('Attribute',$(counter#),'masterfields');
//join data to pk list
join(fields)
load Field1,
 Value as [$(vfield)]
resident datagroup where Attribute='$(vfield)';
next counter#
//cleanup stage
drop table masterfields;
drop table datagroup;

I used the exact same script for 99 "Attributes" across 6000+ "field1"s from an xml repository and it took less than 30 seconds to build the flat table (the script will automatically count how many attributes and field1 values you have and adjust accordingly.

[#7]

Hi,

you can use "generic load" that is the complement of "crosstable load".

For example:

original:
load * INLINE [
Field1, Attribute, Value
A, xx, 1034
A, yy, 4076
A, zz, 34
B, xx, 2345
B, yy, 243
];

temp1:
generic load * resident original;

result:
load distinct Field1 resident original;

drop table original;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'temp1.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i

drop table TableList;

[#8]

Thanks for the suggestions, guys! I should get around to trying something today and I'll verify any and all answers that work...

[#9]

Awesome.  Thanks Alex!!

I was in a similar bind, and your code worked perfectly!

-DJ

[xtrimf]

Hi,

But what if you have multiple values?

For example

Field1     Attribute     Value1 Value2 Value3...

A               xx               1          2          3

A               yy               3          1          2

A               zz               4          5          6

B               xx               7          4          3

B               yy               6          3          7

B               zz               9          4          6

and the table you need to get is:

Field1     xx     yy     zz

A             1      3        4

A             2      1        5

A             3      2        6

etc...

How do you change the code for that?

tnx