
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Data Load Editor: Manipulating a table in the editor
In Qlik Sense, have created a connection to an Oracle database. I would like to load some data, but I want to make some changes to the table before loading it. Here's what the table looks like coming from the database.
Format from database:
Field_Name | ID | char_value |
---|---|---|
field1 | 001 | field1_001 |
field1 | 002 | field1_002 |
field1 | 003 | field1_003 |
field2 | 001 | field2_001 |
field2 | 002 | field2_002 |
field2 | 003 | field2_003 |
field3 | 001 | field3_001 |
field3 | 002 | field3_002 |
And here's what I'd like to load into Qlik Sense.
Desired format to load into Qlik Sense:
ID | field1 | field2 | field3 |
---|---|---|---|
001 | field1_001 | field2_001 | field3_001 |
002 | field1_002 | field2_002 | field3_002 |
003 | field1_003 | field2_003 | NA |
Here's what the script looks like to load the top table:
LIB CONNECT TO 'database';
LOAD "FIELD_NAME",
"ID",
"CHAR_VALUE";
SQL SELECT "FIELD_NAME",
"ID",
"CHAR_VALUE",
FROM database."table";
What do I need to do in the data load editor to load the table on the bottom given the table on the top?
Thanks for your help!
Brian
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The field list order in GENERIC is important. It should be id, field, value. So yours should work as:
Generic LOAD
STUDENTID,
"FIELD_NAME",
"CHAR_VALUE";
-Rob


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This can be handled in QlikView with "Generic Load":
When I try to do the same in QlikSense, the output wasn't as desirable as you would like due to blank field for field3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I appreciate your help, but I don't think that's what I'm looking for. To be more specific, the column Field_Names contains several variables that I would like to load as unique columns. Your output looks good but it's not what I get when I try it. I'm basically trying to pivot the table that I load so that each unique Field_Name is imported as its own column.
Thanks for your help!
Brian


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share your script?
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
LIB CONNECT TO 'database';
Generic LOAD
"FIELD_NAME",
STUDENTID,
"CHAR_VALUE";
SQL SELECT
"FIELD_NAME",
STUDENTID,
"CHAR_VALUE"
FROM "table";
It loads but then treats every unique char_value as it's own column. I want every unique Field_name to be a unique column instead.
Thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
a simple solution could be
a:
LOAD ID,
char_value as field1
FROM [https://community.qlik.com/thread/169443] (html, codepage is 1252, embedded labels, table is @1)
where Field_Name='field1';
join (a) LOAD ID, char_value as field2
FROM [https://community.qlik.com/thread/169443] (html, codepage is 1252, embedded labels, table is @1)
where Field_Name='field2';
join (a) LOAD ID, char_value as field3
FROM [https://community.qlik.com/thread/169443] (html, codepage is 1252, embedded labels, table is @1)
where Field_Name='field3';


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey Massimo,
I think the problem is that Brian has multiple values in char_value column than in his sample table. Although, I might be wrong.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The field list order in GENERIC is important. It should be id, field, value. So yours should work as:
Generic LOAD
STUDENTID,
"FIELD_NAME",
"CHAR_VALUE";
-Rob

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's correct. Thanks a lot to each of you for all of your help. I think Rob's emphasis on the list order in the GENERIC LOAD statement is what did the trick.
This is maybe what sinanozdemir meant, but like I said, I'm a total novice and it's quite possible that it just took a little time to penetrate my thick skull .
