topic Re: link different table with same fields in QlikView

?

xyz1 — Sun, 14 Apr 2019 15:24:08 GMT

Re: link different table with same fields

Anil_Babu_Samineni — Thu, 07 Sep 2017 09:47:39 GMT

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;

?

xyz1 — Sun, 14 Apr 2019 15:21:13 GMT

Re: link different table with same fields

Anil_Babu_Samineni — Thu, 07 Sep 2017 11:05:27 GMT

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;

Re: link different table with same fields

neha_shirsath — Thu, 07 Sep 2017 11:41:57 GMT

See the code in attached file.

?

xyz1 — Sun, 14 Apr 2019 15:19:52 GMT

?

xyz1 — Sun, 14 Apr 2019 15:20:27 GMT

?

xyz1 — Sun, 14 Apr 2019 15:21:46 GMT

Re: link different table with same fields

Anil_Babu_Samineni — Thu, 07 Sep 2017 11:56:54 GMT

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

HTH

Re: link different table with same fields

Anil_Babu_Samineni — Thu, 07 Sep 2017 12:02:42 GMT

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;

Re: link different table with same fields

neha_shirsath — Thu, 07 Sep 2017 12:07:38 GMT

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;

Re: link different table with same fields

Anil_Babu_Samineni — Thu, 07 Sep 2017 12:13:26 GMT

Don't require again use simple Key

Link_Table:

LOAD Key, party_name, date Resident Sales;

Concatenate

LOAD Key, party_name, date Resident Order;

DROP Fields party_name, date From Sales;

DROP Fields party_name, date From Order;

Re: link different table with same fields

trspaete — Thu, 07 Sep 2017 15:42:29 GMT

A little different approach. You wanted to group your results by Quarters. Join the two tables and create a column that calculates the Quarter period based on either the Sales Date or Booking Date of the row. Then Pivot with Party name and Quarter Number with Sums for Sales & Bookings.

Re: link different table with same fields

xyz1 — Fri, 08 Sep 2017 04:54:44 GMT

Thanks Neha

regards

?

xyz1 — Sun, 14 Apr 2019 15:17:12 GMT

Re: link different table with same fields

Anil_Babu_Samineni — Fri, 08 Sep 2017 04:59:04 GMT

NP, Always welcome if you mark Helpful for others