topic Re: Full Outer Join in qlikview script in QlikView

Full Outer Join in qlikview script

suvechha_b — Thu, 21 Nov 2013 09:12:58 GMT

I am unable to get future data of [Fiscal Year]= 2015 and 2016

This is my fact : my first fact contains data of future period_key i.e [Fiscal Year]= 2015 and 2016

"OpenStock":

SQL SELECT period_key,

product_key,

opening_stock_unit,

closing_stock_unit

from JNBVDW02a.[edw].[dbo].[vw_fact_trading_statement];

Qlkmape3:

SQL SELECT

[period_key]

,[product_key]

,[Product Company Code]

,[Division Code]

,[Measure]

,[Main Division Code] as [Main Dimn Code1]

,[Accuracy Type]

,[Accuracy]

,forecast

,actual

,error

,[Division Product Manager Code]

,[Division Product Manager Name]

from [edw].[dbo].[qlk_fact_forecast_accuracy];

Dimension :

DimPeriod:

SQL SELECT

[period_key]

,[Fiscal Year]

,[Fiscal Period]

,[Fiscal Year/Period]

,[Fiscal Year/Month]

,[Fiscal Qtr/Name]

,[Fiscal Month]

,[fiscal_key]

,Left("fiscal_month_key",4) as year

,[fiscal_month_key]

,[Fiscal Month] + ' ' + Left("fiscal_month_key",4) as bucket

FROM JNBVDW02a.[edw].[dbo].[qlk_dim_period] where [Fiscal Year] >=2010;

DimProduct:

LOAD product_key,

"Product Code",

Product,

"Product/Code",

"Brand Code",

Brand,

"Brand/Code",

"Sales Focus Group",

"Product Type Code",

"Product Type",

"Product Type/Code",

"Private Product Manager Code",

"Private Product Manager",

"Private Product Manager/Code",

"Planner",

"D&OP group",

"Short Dated No Days",

"Marketing Item Group",

"Signal/Code",

"Abc Classification",

"Product Supplier",

"Selection Name",

[Std Cost Price],"time_fence";

SQL SELECT *

FROM JNBVDW02a.[edw].[dbo].[qlk_dim_product] where [Product Company Code]='10';

Re: Full Outer Join in qlikview script

Thu, 21 Nov 2013 10:48:58 GMT

Instead of joining Concatenate these two tables....

Re: Full Outer Join in qlikview script

suvechha_b — Thu, 21 Nov 2013 10:54:47 GMT

Can you please explain me ellaborately, as I am new to qlikview.

Re: Full Outer Join in qlikview script

Anonymous — Thu, 21 Nov 2013 16:18:15 GMT

simply use [concatenate] between loads, because it is like union all statement in SQL.

Re: Full Outer Join in qlikview script

MayilVahanan — Thu, 21 Nov 2013 16:24:53 GMT

Concatenate is the key word used in qlikview, to load all the fields from the tables which we try to join.

From help:

If two tables that are to be concatenated have different sets of Fields, Concatenation of two tables can still be forced with the Concatenate prefix. This statement forces concatenation with an existing named table or the latest previously created Logical Table. A concatenation is in principle the same as the SQL UNION statement, but with two differences: first that Concatenate prefix can be used no matter if the tables have identical field names or not; and secondly that no removal of identical records are made.

The syntax is:

concatenate [ (tablename ) ] ( loadstatement | selectstatement )

Ex:

Table1:

Load * from table1;

Concantenate(Table1)

Load * from table2;

In that, we can able to see all fields from both tables in a single table.

Re: Full Outer Join in qlikview script

Anonymous — Thu, 21 Nov 2013 19:21:23 GMT

suvechha.b

Doing such concatenations can often seem a bit counter intuitive, and I find it very hard to explain.

So you may initially need to a do leap of faith and just do it. Assuming it works for you then you try and unravel it & understand. Simplifying your data model to the minimum bare bones for learning purposes will help see what is happening.

When you have any questions on specifics, prepare a sample qvw and ask a new question here on the QlikCommunity attaching your sample qvd.

Best Regards, Bill Markham