Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
;
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
;
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
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.
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.
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.
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
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.