Discussion Board for collaboration related to QlikView App Development.
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:
where the attributes are actually field names...my desired table looks like this:
If you could even provide the name of the script function required to pull this off, that would be a big help. Thanks!
see thee .qvw application
I think you can do it using create chart tool (Chart Type : Pivot Table) by dragging attribute values columnwise.
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.
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.
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:
(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.
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
LOAD distinct Attribute
//count no of distinct attributes
let vfieldnos# = fieldvaluecount('Attribute');
//create pk list
load distinct Field1
//set loop to zero
//loop through every attribute
for counter#=0 to ($(vfieldnos#)-1)
let vfield = peek('Attribute',$(counter#),'masterfields');
//join data to pk list
Value as [$(vfield)]
resident datagroup where Attribute='$(vfield)';
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.
you can use "generic load" that is the complement of "crosstable load".
load * INLINE [
Field1, Attribute, Value
A, xx, 1034
A, yy, 4076
A, zz, 34
B, xx, 2345
B, yy, 243
generic load * resident original;
load distinct Field1 resident original;
drop table original;
FOR i = 0 to NoOfTables()
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'temp1.*');
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (result) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
drop table TableList;
Thanks for the suggestions, guys! I should get around to trying something today and I'll verify any and all answers that work...
Awesome. Thanks Alex!!
I was in a similar bind, and your code worked perfectly!
But what if you have multiple values?
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
How do you change the code for that?