Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I'm trying to connect between date and data
but all Cost fields duplicate all over the date
after I'm trying to do IntervalMatch and the date did not connect to the data
this is the script i was wrrit
Cost123:
// EmpDeparture :
LOAD Distinct
SalesmanID,
"To Date"
FROM [lib://QVDStorage/EmpDeparture]
(qvd)
Where SalesmanID =SalesmanID;
// Concatenate with EmpDeparture table for use on "To Date" field where "To Date" is null
Concatenate
// left join(Cost)
Cost:
LOAD Distinct
SalesmanID,
"From Date",
// where "To Date" is not null don't do any thing and if "To Date" is null put date of today in place of "To Date"
if(Not IsNull("To Date"),"To Date",
if (isnull("To Date"),Today()))as "ToDate",
Cost
FROM [lib://QVDStorage/Cost]
(qvd);
Store Cost123 into [lib://QVDStorage/Cost123];
Drop Table Cost123;
Cost:
LOAD
SalesmanID,
"From Date",
"ToDate",
Cost
FROM [lib://QVDStorage/Cost123]
(qvd)
where not IsNull(Cost);
MinMaxDateT:
LOAD
Max(ToDate) as MaxDate,
Min("From Date") as MinDate
Resident Cost;
LET varMinDate = Num(Peek('MinDate', 0, 'MinMaxDate'));
LET varMaxDate = Num(Peek('MaxDate',0, 'MinMaxDate'));
LET vToday = Num(Today());
TempCalendarT:
LOAD Distinct
$(varMinDate)+IterNo()-1 AS UnitLoadDate
AUTOGENERATE (1) WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
// Concatenate(Cost)
UnitLoadDates:
IntervalMatch (UnitLoadDate) LOAD Distinct MinDate, MaxDate
Resident MinMaxDateT;
Drop Table MinMaxDateT;
Drop Table TempCalendarT;
Hi, in the first table I think you'll need a join to have "To Date" and "From Date" in the same row, if you do a concatenate you just add rows so the 2nd table will always have "To Date"=Null().
You can load Cost123 qvd and create a table with it's fields to check how the date is being loaded.
After the join you can do a resident load of this table to check dates and complete the "To Date" with Today()
And if you want to assign date to cost intervals you will need to use the From-To ranges of each SalesManId, maybe you want the example2 of the IntervalMatchHelp: https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...