Additional field in crosstable load

[mazacini]

Here is my crosstable load:

Crosstable (DCode, Data,2)

LOAD Type,

          PCode,

          [1],

          [2],

          [3],

          [4],

......

          [12]

FROM etc.etc

Now I want to load a column which combines the columns Type, PCode and DCode as NEWCOL.

So after [12], I have a new line

Type&PCode&DCode as NEWCOL

But when I try to do that, I get a message:

Field Not Found - <DCode>

Ideas anyone?

[johnw · Accepted solution]

DCode doesn't exist until you finish the crosstable load, because you're building DCode in the crosstable load.  So you can't refer to it in the load, because it doesn't exist yet.  If I've understood what you want, you could left join after the crosstable load. 

LEFT JOIN (Whatever)
LOAD DISTINCT
Type
,PCode
,DCode
,Type&PCode&DCode as NEWCOL
RESIDENT Whatever
;

[mazacini]

Hi John

I get what you are saying about DCode.

But I haven't used RESIDENT before. Would you mind explaining how it works?

Many thanks

Joe

[johnw]

RESIDENT loads from a table that you're already loaded into memory.

Data:

LOAD * INLINE [

Key, Value1

A, 50

B, 100

];

LEFT JOIN (Data)

LOAD

Key

,Value1 + 10 as Value2

RESIDENT Data

;

Will give you this table:

Data:

Key, Value1, Value2

A, 50, 60

B, 100, 110

I'm sure there's a lot more information in the documentation.  In the specific example above, you'd be better off doing the calculation in the initial load.  In many other examples, you're better off doing the calculation in a preceding load.  But in some cases, such as a crosstable load, neither of those options is available, and the left join resident is a reasonable approach.

[mazacini]

Hi John

Thanks for the clarification - It allows me to better understand the solution proposed in your first response.

Would you believe that there is NO reference to the RESIDENT in the tutorial. The manual does refer to it, but only by way of providing syntax, and not by explaining its function.

This is frustrating for a non technical person like myself.

Thats why the community is so great.

Joe

I will test your solution in the morning, when I assume I wll be able to mark it correct.

[johnw]

Wow.  Yeah, all I could find with a quick glance through the help file was this:

resident is used if data should be loaded from a previously loaded input table.

That hardly seems like adequate documentation, particularly for something so very fundamental.  I use resident loads constantly.  It tends to be better for performance when you can avoid them, but you can't always avoid them.  I can almost never avoid them.

[mazacini]

Hi John

If you had a moment, coudl you possibly explain how DISTINCT works in the above?

I had another issue with LEFT JOIN here

http://community.qlik.com/message/167004#167004

In fact I think I resolved this by using DISTINCT - but I have no idea why!

Rgds

Joe

[johnw]

Let's say your data looks like this.

Type, PCode, DCode
A, B, C
A, B, C
D, E, F

If you don't use distinct, each of the two ABC rows will join to each of the two ABC rows, thus creating four ABC rows:

Type, PCode, DCode, NEWCOL
A, B, C, ABC
A, B, C, ABC
A, B, C, ABC
A, B, C, ABC
D, E, F, DEF

This sort of error is often difficult to spot, because QlikView only shows you unique combinations of fields when you display data, so those four rows would look like a single row most of the time.

If you can guarantee that values won't repeat like this, you could eliminate the distinct, which would probably speed up the script slightly.