Data Load Editor: Manipulating a table in the editor

[#1]

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

[sinanozdemir]

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

[#3]

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

[sinanozdemir]

Can you share your script?

Thanks

[#5]

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.

[maxgro]

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';

[sinanozdemir]

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.

[rwunderlich · Accepted solution]

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

[#9]

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 .