Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cseward1963
Creator
Creator

help with a JOIN in a LOAD SCRIPT

See below, this LOAD from Resident CPCfile is already in my load script.

LOAD

"DATE" as "DATE",

month("DATE") as tmonth,

num(month("DATE")) as tmonthnum,

year("DATE") as tyear,

day("DATE") as tday     

   Resident CPCFile;

I also have this little file available that contains field schooldays which is not yet in my load script.   I want to join below with above so I can include schooldays.  How do I do the JOIN in the load script?  I suppose I could JOIN on tmonth and tyear to access schooldays like in SQL.   PLEASE HELP !!!

LOAD

    tmonth,

    tyear,

    schooldays

FROM [lib://Data files/RadnorMappingTables.xls]

(biff, embedded labels, table is schooldays$);

3 Replies
Anonymous
Not applicable

This can be useful for you: Different Join Functions in Qlikview

In this case you can make a key by yearmonth. Please check first the date format in both of your tables.

You should rename the fields with the same name in the second table in order to avoid to have a synthetic key in your model.

Table1:

LOAD

year("DATE") &month("DATE")  as %DateKey

"DATE" as "DATE",

month("DATE") as tmonth,

num(month("DATE")) as tmonthnum,

year("DATE") as tyear,

day("DATE") as tday   

  Resident CPCFile;


Left Join (Table1)


LOAD

    tyear&tmonth as %DateKey

    tmonth as SchoolDaysTMonth,

    tyear as SchoolDaysTYear,

    schooldays

FROM [lib://Data files/RadnorMappingTables.xls]

(biff, embedded labels, table is schooldays$);


Hope it helps!

Josefina

JustinDallas
Specialist III
Specialist III

Also, keep in mind when you are making a key, it always helps to have some kind of delimiter between your key components.  Other wise you end up key collisions which will take FOREVER to track down.  Below is a simple example

GimmeCollisions:

LOAD *, Month & Day AS 'KeyCollision'

;

LOAD * Inline

[

    'Month', 'Day'

      11    ,    2

      1      ,    12

]

;

The value of KeyCollision in the above table would be '112' for both rows.  So I always do something like

Month & '-' & Day AS NoCollision

or

Month & '/' & Day As NoCollisionsNoSiree

Anonymous
Not applicable

That's true! Thanks for noticing it