topic Row values as fields (opposite of crosstable?) in QlikView

Row values as fields (opposite of crosstable?)

Sat, 21 Aug 2010 04:54:56 GMT

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!

Row values as fields (opposite of crosstable?)

Sat, 21 Aug 2010 07:40:51 GMT

see thee .qvw application

thanks

zaman

Row values as fields (opposite of crosstable?)

shumailh — Sat, 21 Aug 2010 10:36:15 GMT

Jason,

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.

Shumail

Row values as fields (opposite of crosstable?)

Sun, 22 Aug 2010 03:58:12 GMT

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?

Row values as fields (opposite of crosstable?)

vgutkovsky — Sun, 22 Aug 2010 09:51:04 GMT

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,

Row values as fields (opposite of crosstable?)

Sun, 22 Aug 2010 12:48:34 GMT

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.

Row values as fields (opposite of crosstable?)

Mon, 23 Aug 2010 14:21:35 GMT

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;

Row values as fields (opposite of crosstable?)

Mon, 23 Aug 2010 14:35:39 GMT

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

Row values as fields (opposite of crosstable?)

Thu, 22 Sep 2011 21:35:26 GMT

Awesome. Thanks Alex!!

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

-DJ

Row values as fields (opposite of crosstable?)

xtrimf — Tue, 27 Sep 2011 09:23:18 GMT

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

Row values as fields (opposite of crosstable?)

Tue, 04 Oct 2011 23:51:38 GMT

Generic Load is the correct answer.

Re: Row values as fields (opposite of crosstable?)

Wed, 03 Apr 2013 12:02:56 GMT

You could try something like the following:

TableA:

Load

Field1,

Attribute,

Value1 & '_' & Value2 & '_' & Value3 as ConcatValues

From......;

let zFieldCount = FieldValueCount('Attribute');

for z = 1 to $(zFieldCount)
let zFieldName = FieldValue('Attribute',$(z));

      load
          Field1,
           subfield(ConcatValues,'_') as [$(zFieldName)]
      resident TableA
      where Attribute = '$(zFieldName)';

next z

Re: Row values as fields (opposite of crosstable?)

marcelo_7 — Wed, 16 Oct 2013 10:43:58 GMT

This is what I was looking for. Thanks!

Re: Row values as fields (opposite of crosstable?)

ljackson — Wed, 16 Oct 2013 14:06:30 GMT

I agree with shumailh, I would use a pivot table and drag the values to the descired locations

Re: Row values as fields (opposite of crosstable?)

hschultz — Tue, 07 Jan 2014 07:40:35 GMT

Great answer! Thank you for solving my problem

Re: Row values as fields (opposite of crosstable?)

pandiarajan — Tue, 05 Aug 2014 09:10:38 GMT

Dear Alessandro,

Same Script i was use but table not found error comes

Re: Row values as fields (opposite of crosstable?)

Thu, 24 Nov 2016 14:01:02 GMT

exactly what I wanted !!

Re: Row values as fields (opposite of crosstable?)

sebastian_fager — Wed, 23 Oct 2019 08:41:04 GMT

Hi,

This script works until the loop to join evertything togheter. It won't apply the left join, so i end up with a lot of tabells. What can i do?