help with a JOIN in a LOAD SCRIPT

[cseward1963]

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$);

[Anonymous]

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]

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]

That's true! Thanks for noticing it