Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Can you share your script?
Thanks
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.
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';
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.
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
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 .