topic Re: How to join two tables in qlikview script in QlikView

How to join two tables in qlikview script

suvechha_b — Thu, 23 Jul 2015 10:04:16 GMT

Please see the example :

I want to join "BUDGET" and "BUDGETC" in table "BUDGET"

"BUDGET":

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\Budget.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10

;

load

'F' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

0 as customer_key ,

'F' as [Type Indicator],

[Quantity],

[Sales Value],

SEP

// ,[Trans Date]

FROM [..\00 QVD Source\Forecast.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10

;

left join ("BUDGET")

LOAD

fiscal_year,

fiscal_period,

[Trans Date]

FROM

[..\20 Master Data\Fin to Cal Date Conversion.xls]

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

"BUDGETC":

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\BudgetCurrent.qvd](qvd)

;

left join ("BUDGETC")

SQL SELECT DISTINCT

a.fiscal_year ,

a.fiscal_period ,

MAX(calendar_key) AS [Trans Date]

FROM dbo.vw_dim_calendar a

inner join dbo.dim_period b

on a.fiscal_year=b.fiscal_year

and a.fiscal_period=b.fiscal_period

where period_key=$(vMaxPeriodkey)

GROUP BY a.fiscal_year,a.fiscal_period,trading_date;

Re: How to join two tables in qlikview script

sunny_talwar — Thu, 23 Jul 2015 10:14:20 GMT

Try this may be

"BUDGET":

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\Budget.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10;

Concatenate ("BUDGET")

load

'F' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

0 as customer_key ,

'F' as [Type Indicator],

[Quantity],

[Sales Value],

SEP

// ,[Trans Date]

FROM [..\00 QVD Source\Forecast.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10;

left join ("BUDGET")

LOAD

fiscal_year,

fiscal_period,

[Trans Date]

FROM

[..\20 Master Data\Fin to Cal Date Conversion.xls]

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

"BUDGETC":

NoConcatenate

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\BudgetCurrent.qvd](qvd);

left join ("BUDGETC")

SQL SELECT DISTINCT

a.fiscal_year ,

a.fiscal_period ,

MAX(calendar_key) AS [Trans Date]

FROM dbo.vw_dim_calendar a

inner join dbo.dim_period b

on a.fiscal_year=b.fiscal_year

and a.fiscal_period=b.fiscal_period

where period_key=$(vMaxPeriodkey)

GROUP BY a.fiscal_year,a.fiscal_period,trading_date;

Concatenate ("BUDGET")

LOAD *

FROM "BUDGETC";

DROP Table "BUDGETC"

Re: How to join two tables in qlikview script

avinashelite — Thu, 23 Jul 2015 10:16:22 GMT

Did not get the excat requirement try like this :

"BUDGET":

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\Budget.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10

;

load

'F' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

0 as customer_key ,

'F' as [Type Indicator],

[Quantity],

[Sales Value],

SEP

// ,[Trans Date]

FROM [..\00 QVD Source\Forecast.qvd](qvd)

where fiscal_year = 2015 and fiscal_period >= 10

;

left join ("BUDGET")

LOAD

fiscal_year,

fiscal_period,

[Trans Date]

FROM

[..\20 Master Data\Fin to Cal Date Conversion.xls]

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

NoConcatenate

TEMP_BUDGETC:

load

'B' & rowno() as [Transaction Index],

fiscal_year,

fiscal_period,

division_key ,

product_key ,

customer_key ,

'B' as [Type Indicator],

units as [Quantity],

value as [Sales Value],

sep as SEP

FROM [..\00 QVD Source\BudgetCurrent.qvd](qvd)

;

left join ("BUDGETC")

SQL SELECT DISTINCT

a.fiscal_year ,

a.fiscal_period ,

MAX(calendar_key) AS [Trans Date]

FROM dbo.vw_dim_calendar a

inner join dbo.dim_period b

on a.fiscal_year=b.fiscal_year

and a.fiscal_period=b.fiscal_period

where period_key=$(vMaxPeriodkey)

GROUP BY a.fiscal_year,a.fiscal_period,trading_date;

join(BUDGET)

LOAD * From

resident

TEMP_BUDGETC;

Drop table TEMP_BUDGETC;