Linktable Query

[Nikita_Ben08]

i have 3 table . How to  create  linktable from  alpha and echo table  so that when user click on date  like mar-25 it will change data from both table

alpha table is transaction table

alphalcin	alpha date	alpha limit ID	alpha country	amount
A	1/9/2025	lim1	SG	100
B	1/10/2025	lim2	CN	200
C	1/11/2025	lim3	IN	300

echo table is transaction table

echo lcin	date	echolimitID	echo country	amount
x	1/9/2025	lim3	SG	100
y	1/5/2025	lim4	CN	200
z	1/10/2025	lim5	IN	300

detail  table include relation of  alpha and echo

alphalcin	echo lcin	echo name	alpha name	echo segment	alphasegment	industry	alpha country	echo country
A	x	nikil	ahay	l	f	xx	SG	SG
B	y	ajit	rocky	m	l	xx	CN	CN
C	z	sujit	rani	f	m	xx	IN	IN

[QFabian · Accepted solution]

Hi @Nikita_Ben08 , one option could be with no linktable between Data and Detail. Check this script and see if works as an option for you.

Script :

///Source Data samples

alpha:

Load * INLINE [

alpha_lcin, alpha_date, alpha_limit_ID, alpha_country, amount

A, 1/9/2025, lim1, SG, 100

B, 1/10/2025, lim2, CN, 200

C, 1/11/2025, lim3, IN, 300

];

echo:

Load * INLINE [

echo_lcin, date, echo_limit_ID, echo_country, amount

x, 1/9/2025, lim3, SG, 100

y, 1/5/2025, lim4, CN, 200

z, 1/10/2025, lim5, IN, 300

];

detail:

Load * INLINE [

alpha_lcin, echo_lcin, echo_name, alpha_name, echo_segment, alpha_segment, industry, alpha_country, echo_country

A, x, nikil, ahay, l, f, xx, SG, SG

B, y, ajit, rocky, m, l, xx, CN, CN

C, z, sujit, rani, f, m, xx, IN, IN

];

///Joining both echo and alpha in one table

Data:  //first 'alpha'

Load 

'Alpha' as Type,   //new filed to differentiate between alpha and echo

upper(alpha_lcin) as LCIN,

alpha_date as Date,

    alpha_limit_IDasLimit_ID,

    alpha_countryasCountry,

    amount as Amount

Resident alpha;

drop table alpha;

Load   //then 'echo',  this resulting dataset structure 'echo' is equal than previous one 'alpha', so auto concatenate applies

'Echo' as Type,  //new filed to differentiate between alpha and echo

upper(echo_lcin) as LCIN,

    dateasDate,

    echo_limit_IDasLimit_ID,

    echo_countryasCountry,

    amount as Amount

Resident echo;

drop table echo;

//Same with detail table

Detail:

Load

    upper(alpha_lcin)asLCIN,

    capitalize(alpha_name) as Name,

    upper(alpha_segment) as Segment

Resident detail;

Detail:

Load

    upper(echo_lcin)asLCIN,

    capitalize(echo_name)asName,

    upper(echo_segment) as Segment

Resident detail;

drop table detail;

exit script;

Relations :