Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
xyz1
Contributor III

?

.

1 Solution

Accepted Solutions
neha_shirsath
Valued Contributor

Re: link different table with same fields

SALES:

LOAD party_name & '|' & date as Key,

  party_name,

     sales_amount,

     date

FROM

[2months.xlsx]

(ooxml, embedded labels, table is Sheet3);

ORDER:

LOAD party_name & '|' & date as Key,

  party_name,

     booking_amount,

     date

FROM

[2months.xlsx]

(ooxml, embedded labels, table is Sheet4);

LINK_TABLE:

LOAD party_name & '|' & date as Key,

  party_name,

  date

Resident SALES;

DROP Fields party_name, date From SALES;

Concatenate

LOAD party_name & '|' & date as Key,

  party_name,

  date

Resident ORDER;

DROP Fields party_name, date From ORDER;

Master_Cal:

Load distinct date  ,

     //Floor(Claim_Process_Date) as DateNum ,

     QuarterStart(date) as QuaterStartDate,

  Day(date) as Day,

  Month(date) as Month,

  Year(date) as Year,

  Monthname(date) as MonthYear,

  num(month(date)) as Month_Number,

  Week(date) as Week,

  Month(date) &'-'& Year(date) as YearMonth ,

if(num(month(date))>=4 and num(month(date))<=6,'Q1',

if(num(month(date))>=7 and num(month(date))<=9,'Q2',

if(num(month(date))>=10 and num(month(date))<=12,'Q3',

if(num(month(date))>=1 and num(month(date))<=3,'Q4')))) as Quarter,

if(num(month(date))<=3,Year(date)-1&'-'&right(Year(date),2),Year(date)&'-'&right(year(date)+1,2)) as FinancialYear,

if(num(month(date))<=3,num(month(date))+9,num(month(date))-3) as FinancialMonthNumber

Resident LINK_TABLE;

15 Replies

Re: link different table with same fields

May be create link table like below

Sales:

LOAD party_name, sales_amount, date, party_name & '|' & date as Key

FROM

Sales_Table;

Order:

LOAD party_name, booking_amount, date, party_name & '|' & date as Key

FROM

Order_Table;

//Creating Link Table for 2 fact tables which is Sales and Order

Link_Table:

Load DISTINCT Key, sales_amount resident Sales;

Concatenate(Link_Table)

Load DISTINCT Key, booking_amount resident Order;


// Here, I am removing the fields because we already generate the Key for Matching numbers to each


Drop Fields part_name, date from Sales;

Drop Fields part_name, date from Order;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
xyz1
Contributor III

?

.

Re: link different table with same fields

Try this?

Master:

Load party_name,category,market,im_code,im_descr,qty,rate,basicvalue,city,state,region,continent,date, party_name & '|' & date as Key

FROM [lib://Sales/SALESDOM.xlsx]

(ooxml, embedded labels, table is [RAW EXTRACT]);

Order:

LOAD party_name,di_qty, date, rate_per_mt, pending_qty, party_name & '|' & date as Key

    FROM [lib://Sales/Bookings Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

Link_Table:

Load DISTINCT Key, category,market,im_code,im_descr,qty,rate,basicvalue,city,state,region,continent, Today() as Today_Date resident Master;

Concatenate(Link_Table)

Load DISTINCT Key, di_qty, rate_per_mt, pending_qty resident Order;

Drop Fields party_name, date from Master1;

Drop Fields party_name, date from Order;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
neha_shirsath
Valued Contributor

Re: link different table with same fields

See the code in attached file.

xyz1
Contributor III

?

.

xyz1
Contributor III

?

.

xyz1
Contributor III

?

.

Re: link different table with same fields

Please follow the same https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

HTH

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: link different table with same fields

This is working for me?

Sales:

LOAD party_name & '|' & date as Key, party_name, sales_amount, date Inline [

party_name  ,     sales_amount    ,           date

X                 ,                        2   ,                       2/2/2017

Y                 ,                        3   ,                        6/2/2017

Z                 ,                        8   ,                        13/1/2017

A                 ,                       1    ,                       5/7/2017

];

Order:

LOAD party_name & '|' & date as Key, party_name,  booking_amount,  date Inline [

party_name  ,     booking_amount      ,                      date

X                        ,                 2          ,                              2/6/2017

A                              ,          1          ,                               5/3/2017

];

Link_Table:

LOAD party_name & '|' & date as Key, party_name, date Resident Sales;

Concatenate

LOAD party_name & '|' & date as Key, party_name, date Resident Order;

DROP Fields party_name, date From Sales;

DROP Fields party_name, date From Order;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)