Qlik Community

Qlik Sense Data Connectivity

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
MVP & Luminary
MVP & Luminary

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

View solution in original post

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.

MVP & Luminary
MVP & Luminary

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

View solution in original post

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 .