Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to join two tables in qlikview script

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;

2 Replies
Highlighted

Re: How to join two tables in qlikview script

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

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;