Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Not applicable

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_NameIDchar_value

field1

001field1_001
field1002field1_002
field1003field1_003
field2001field2_001
field2002field2_002
field2003field2_003
field3001field3_001
field3002field3_002

And here's what I'd like to load into Qlik Sense.

Desired format to load into Qlik Sense:

IDfield1field2field3
001field1_001field2_001field3_001
002field1_002field2_002field3_002
003field1_003field2_003NA

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

1 Solution

Accepted Solutions

Re: Data Load Editor: Manipulating a table in the editor

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

8 Replies
sinanozdemir
Valued Contributor III

Re: Data Load Editor: Manipulating a table in the editor

This can be handled in QlikView with "Generic Load":

Capture.PNG

Capture2.PNG

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

Capture.PNG

Not applicable

Re: Data Load Editor: Manipulating a table in the editor

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
Valued Contributor III

Re: Data Load Editor: Manipulating a table in the editor

Can you share your script?

Thanks

Not applicable

Re: Data Load Editor: Manipulating a table in the editor

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.

MVP
MVP

Re: Data Load Editor: Manipulating a table in the editor

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



1.png

sinanozdemir
Valued Contributor III

Re: Data Load Editor: Manipulating a table in the editor

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.

Re: Data Load Editor: Manipulating a table in the editor

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

Not applicable

Re: Data Load Editor: Manipulating a table in the editor

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 .

Community Browser