Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
What's the correct syntax for joining two inline tables?
Supposed I have these:
STATE:
LOAD * INLINE
[
STATE
MA
IL
FL
CA
];
CITY:
LOAD * INLINE
[
STATE, CITY
MA, BOSTON
IL, CHICAGO
FL, MIAMI
];
[ CODE HERE ]
How do I write the code to join STATE and CITY and place the code inside the [ CODE HERE ] area? I know you can add
the JOIN clause before the "CITY:" in above like but I want to write the statement AFTER the two tables are created.
LEFT JOIN (STATE)
CITY:
LOAD * INLINE
[
STATE, CITY
MA, BOSTON
IL, CHICAGO
FL, MIAMI
];
Thanks!
Not sure what you trying to do, but may be this;
STATE:
LOAD * INLINE
[
STATE
MA
IL
FL
CA
];
CITY:
LOAD * INLINE
[
STATE, CITY
MA, BOSTON
IL, CHICAGO
FL, MIAMI
];
JOINED_TABLE:
NOCONCATENATE
LOAD *
RESIDENT STATE;
LEFT JOIN (JOINED_TABLE)
LOAD
*
RESIDENT CITY;
DROP TABLES, CITY, STATE;
You can join in the second the Load statement before.
STATE:
LOAD * INLINE
[
STATE
MA
IL
FL
CA
];
Left Join (STATE)
CITY:
LOAD * INLINE
[
STATE, CITY
MA, BOSTON
IL, CHICAGO
FL, MIAMI
];
Why you need to join after two tables loaded ?
what you are trying to achieve, by doing so..?
Your sample worked. Well two reason I want to do this. First, I want to learn how to write this syntax.
Second, I may have 5 or 10 tables in the app. But, there are instances where I may need to join Table1,
Table7 and Table10 in order to produce Table11 and the join needs to happen after all the tables have been created.
Thanks
One question I have is: what does "NOCONCATENATE" do as in ?
JOINED_TABLE:
NOCONCATENATE
LOAD *
RESIDENT STATE;
Since I am doing a LOAD *, I am going to bring all the fields from STATE table without changing their name it will autoconcatenate it to the state table if I don't add NOCONCATENATE. NOCONCATENATE tells QlikView to not append it to STATE and create a new table named JOINED_TABLE.
generally in qlikview, if two tables with identical field sets they would normally be Concatenation automatically.
The NoConcatenate prefix forces two tables with identical field sets to be treated as two separate internal tables.
OK, I just removed "NOCONCATENATE". When I ran it, I got an "Table Not Found" error. I did it as a test.
yup , testing is a good way to learn new things.