How can I LOAD a row-based attribute database table into a standard column-based structure?

Wed, 13 Oct 2010 20:41:33 GMT

Hi all, MyTable structure in the database is as follows:
RecordKey | FieldTag | FieldValue
10 | Name | Frank
10 | Age | 22
10 | Sex | Male
10 | Zip | 12345
20 | Name | Mary
20 | Age | 39
20 | Sex | Female
20 | Zip | 54321
...etc...

I want to LOAD it into QlikView as a more standard columnar structure like this:
ID | Name | Age | Sex | Zip
10 | Frank | 22 | Male | 12345
20 | Mary | 39 | Female | 54321
...etc...

I suspect this is because the software vendor implemented a proprietary XML data acces layer for the dependent application. However, I want to generate some reports and it is easier to work with a standard columnar structure. What is the most efficient way to do this at LOAD time in QlikView 9? I can't seem to figure out the more advanced LOAD scripting functions. Thanks in advance!!

How can I LOAD a row-based attribute database table into a standard column-based structure?

Wed, 13 Oct 2010 22:07:17 GMT

Hi stephen, it seems you have a cube-like structure there. I haven´t worked with "generic load" much, so I can't tell about performance. Generic load gives the effect you are looking for, however, it doesn't not build a single table as joining could do, it actually manages the relations through data model linking. The second option, as you might guessed, would be joining, I left the code of both approaches here, as well as an enclosed qvw, so you can see which one fits you better.

//1st option Use generic load

//Data:
//generic load * inline [
//RecordKey, FieldTag, FieldValue
//10, Name, Frank
//10, Age, 22
//10, Sex, Male
//10, Zip, 12345
//20, Name, Mary
//20, Age, 39
//20, Sex, Female
//20, Zip, 54321];

//2nd option Use joining

set v_attr_list = 'Name', 'Age', 'Sex', 'Zip';
set v_attr_first = 'Name';

Data:
load * inline [
RecordKey, FieldTag, FieldValue
10, Name, Frank
10, Age, 22
10, Sex, Male
10, Zip, 12345
20, Name, Mary
20, Age, 39
20, Sex, Female
20, Zip, 54321];

for each attr in $(v_attr_list)

if '$(attr)' = '$(v_attr_first)' then

NewData:
Load RecordKey,FieldValue as $(attr)
resident Data
where FieldTag = '$(attr)';

else

JoinData:
join(NewData)
Load RecordKey,FieldValue as $(attr)
resident Data
where FieldTag = '$(attr)';

end if

drop table Data;

If you choose the joining approach, perhaps the "keep" statement can be faster than a where clause, at least, it has happend to me, when working with large amount of data.

Regards

topic How can I LOAD a row-based attribute database table into a standard column-based structure? in QlikView

How can I LOAD a row-based attribute database table into a standard column-based structure?

How can I LOAD a row-based attribute database table into a standard column-based structure?