Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Row values as fields (opposite of crosstable?)

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:

Field1AttributeValue
Axx1034
Ayy4076
Azz34
Bxx2345
Byy2435


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

Field 1xxyyzz
A1034407634
B23452435--


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

17 Replies
Not applicable
Author

hi

see thee .qvw application

thanks

zaman

shumailh
Creator III
Creator III

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

Not applicable
Author

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? Smile

vgutkovsky
Master II
Master II

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,

Not applicable
Author

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.

Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

Awesome.  Thanks Alex!!

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

-DJ

xtrimf
Creator
Creator

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