HOW TO APPEND TWO TABLES THAT SHARE TWO FIELDS

[#1]

Hi

Im a beginner when it comes to script lanaguage on Qlikview. I have two tables, MasterTurnobver & Turnoverbloomberg.

Both tables share two common fields key_turnoverstatic and turnover date

In access I can easily append these fields, but how can i do that in Qlikview? Evenutally I will need to consolidate this table so I can link it to a reference table...

MasterTurnover:
SQL SELECT "msamk_id",
"msamk_ticker" as Key_Turnoverstatic,
"msamk_isin",
"msamk_dateimported",
"msamk_startdate" as TURNOVER_DATE,
"msamk_enddate",
"msamk_broker",
"msamk_currency",
"msamk_srcname",
"msamk_markitname",
"msamk_turnoverbroker",
"msamk_turnovertotal",
"msamk_brokerpercent",
"msamk_brokerrank",
"msamk_1st",
"msamk_2nd",
"msamk_3rd",
"SSMA_TimeStamp"
FROM msamarkit.dbo.msamarkit;

CONCATENATE ("MasterTurnover")

Turnoverbloomberg:
SQL SELECT "mastproav_id",
BBTicker as Key_Turnoverstatic,
"FUND_TOTAL_ASSETS",
"FUND_TOTAL_ASSETS_CRNCY",
"FUND_TOTAL_ASSETS_EUR",
"EQY_TURNOVER",
CRNCY,
"EQY_TURNOVER_EUR",
"PX_LAST",
"mastproav_Impid",
"mastproav_date" as TURNOVER_DATE,
"FUND_TOTAL_ASSETS_DT",
"FUND_NET_ASSET_VAL",
"NAV_CRNCY",
"FUND_NET_ASSET_VAL_EUR",
"FUND_NAV_DT",
"EQY_SH_OUT"
FROM AUM.dbo.masterprodaumvol;

[Miguel_Angel_Baeyens]

Hi,

Just as you are doing, it seems fine to me, although I always prefer to use "preceding load" statements, to distinguish every moment which fields come from the data base and which ones I'm actually loading into QlikView.

Concatenating both tables will append the contents of one to the other, so there will be no joins or data reduction, which may be useful for you. In your sample code, there's no need for label "Turnoverbloomberg:" as for all purposes, the result will be one table.

Regards.

[#3]

Hi what do you mean by preceding load statements? I thought I was loading everything from the database and everytime I need to refresh the table I need to reload the data from sequel server

Also after concatenating the table I need to formate the turnover date field

Is it correct to do this after the concatenation

Table1:
LOAD

*,
Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
year(TURNOVER_DATE) as Tyear

Resident MasterTurnover

[Miguel_Angel_Baeyens]

ANGUYEN100 wrote:Hi what do you mean by preceding load statements? I thought I was loading everything from the database and everytime I need to refresh the table I need to reload the data from sequel server

It's just a suggestion, not a requirement. I mean your script should look something like

Table1:LOAD Field1, Field2;SQL SELECT Field1 Field2FROM Database;

ANGUYEN100 wrote:Is it correct to do this after the concatenation

There's no need to do a load resident since you can format your date fields in the load statement as

Table1:LOAD Field1, Field2, Date(DateField) AS DateField Month(Date(DateField)) AS Month, Year(Date(DateField)) AS Year;SQL SELECT Field1, Field2, DateFieldFROM Database;

You can create a master calendar which will likely help you in your chart design.

Hope that helps.

[#5]

Hi

Thanks for the trip

What's the difference between Load and the SQL select

[#6]

Wether you use preciding LOAD or not data will come but Qlikview's standard prefers

*****************************

LOAD a,

b,

c;

SQL SELECT *

FROM yourtable;

********************************

This gives you flexibility to comment a field or uncomment it without changing sql statement. Also many transformation functions you can use in LOAD which database sql queries do not support.

[Miguel_Angel_Baeyens]

Hello,

Basically, LOAD is what you actually load into your application, while SQL is what you pull from your database. Think of them as an input (LOAD) and an output (SQL).

The "Preceding load" is _just_ a LOAD for the same fields you are pulling from your database, written by QlikView saving you time.

Regards.