Solved: ? - Qlik Community

xyz1 · ‎2017-09-07

.

neha_shirsath · ‎2017-09-07

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;

View solution in original post

Anil_Babu_Samineni · ‎2017-09-07

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

xyz1 · ‎2017-09-07

.

Anil_Babu_Samineni · ‎2017-09-07

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

neha_shirsath · ‎2017-09-07

See the code in attached file.

xyz1 · ‎2017-09-07

.

xyz1 · ‎2017-09-07

.

xyz1 · ‎2017-09-07

.

Anil_Babu_Samineni · ‎2017-09-07

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

HTH

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anil_Babu_Samineni · ‎2017-09-07

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful