Concatenate tables with dates

[Randomize1978]

Hello,

i have a situation where i need to concatenate Sales table with objectives table.

both tables has a lot of dimensions ,part of them are the same.

i did it all but one dimension that I've tried to concat w/o success.

dates-in the Sales table it is sales by day while in the Objective it is-from date - to date.

concatenate it one to another is  a bit problematic from several reasons:

1.the objective table can contain years from 2015-2025- iterno() while function is running for long time,didnt managed to get it done.

2.The user neer to have am option to click on 2025 for example and to see the objective(without sales of course)which is impossible cause the iterno() while function is based on the sales that ends in 2020.

tried in addition to make an auto calendar but there are more then 100 million lines and didn't managed to do it in a Intervalmatch as well.

i'll be glad for any advice/script example how to get it done.

thank you very much

[eliran]

Hi,

You mentioned interval match, that's part of the solution

I would go for a link table that holds the similliar dimensions, date, from, to.

Getting the from / to will be used with interval match.

Final layout suppose to be as follows:

Sales : Index_Sales (similliar dimensions + date) and other fields

Target: Index_Target + Target_Value

Linke table: Dimensions, Date, From, To

See the attached code, created the sales and targets manually, you can naturally replace it with yours.

Spoiler

Transactions:
load Dim1&'|'&Dim2&'|'&Dim3&'|'&Date as Index_Sales,*;
Load
TransLineID,
TransID,
Date,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
date(Today()+IterNo()+floor(Rand()*100)) as Date,
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;

Targets:
load Dim1&'|'&Dim2&'|'&Dim3&'|'&From&'|'&To as Index_Targets,*;
LOAD
Dim1,Dim2,Dim3,sum(Expression1) as Target,
date(Today()+floor(Rand()*10)) as From,
date(Today()+floor(Rand()*100)+20) as To
Resident Transactions
group by Dim1, Dim2, Dim3;

LinkTable:
IntervalMatch ( Date,Dim1,Dim2,Dim3 )
LOAD Distinct From, To,Dim1,Dim2,Dim3
Resident Targets;

left join(LinkTable)
load
Distinct
Dim1&'|'&Dim2&'|'&Dim3&'|'&From&'|'&To as Index_Targets,
Dim1&'|'&Dim2&'|'&Dim3&'|'&Date as Index_Sales,
Dim1,Dim2,Dim3,From,To, Date
Resident LinkTable;

drop fields Dim1,Dim2,Dim3,From,To from Targets;
drop fields Dim1,Dim2,Dim3,Date from Transactions;

I hope it helps,

Eliran.

[Randomize1978]

thank you,however i didnt manage to understand the things you did with the below quete i will be very thankfull to understand it.:

"mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;"

and the script is stopped when looking for a link table.

[Brett_Bleess]

See the following Design Blog post regarding the Link Table:

https://community.qlik.com/t5/Qlik-Design-Blog/Concatenate-vs-Link-Table/ba-p/1467569

For the others, I would use the Help site to try to understand and reply back if you still have further questions after that, but you may find further information on some of those in the Design Blog  area too.

Regards,
Brett