topic Data Load Editor: Manipulating a table in the editor in Connectivity & Data Prep

Data Load Editor: Manipulating a table in the editor

Mon, 22 Jun 2015 15:51:29 GMT

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

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

sinanozdemir — Mon, 22 Jun 2015 18:37:23 GMT

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

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

Mon, 22 Jun 2015 19:10:39 GMT

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

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

sinanozdemir — Mon, 22 Jun 2015 22:42:03 GMT

Can you share your script?

Thanks

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

Tue, 23 Jun 2015 11:41:06 GMT

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.

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

maxgro — Tue, 23 Jun 2015 12:01:58 GMT

a simple solution could be

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

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

sinanozdemir — Tue, 23 Jun 2015 12:41:57 GMT

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

rwunderlich — Tue, 23 Jun 2015 14:37:04 GMT

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

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

Tue, 23 Jun 2015 15:02:57 GMT

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 .