Hi,
I have a different set of costs I need to apply to my data model, and I've tried various statements with no success. The following block of code is what I am starting with, and this on its own works just fine. You can see the cost rate is £4.00 here.
CONCATENATE(CostValue)
LOAD
Text("Placement ID") as "Placement ID",
// "Date",
Date(Date#("Date",'YYYY-MM-DD'),'DD/MM/YYYY') as Date,
(Sum(Impressions)/1000) * 4.00 as [Cost-Per-Day]
FROM [lib://Output Files/Openreach/GCM/*.qvd] (qvd)
WHERE
Match([Site (DCM)],'crimtan.com','Programmatic Ads - Crimtan Agency')
GROUP BY Date, "Placement ID";
My approach is to have this block of code repeat 3 times, and I insert a different cost rate in each. You see the £4.00 rate in the above example. In my other two tables, it'll be £9 and £10. The differentiating factor will be the dates, so I wanted to insert a WHILE statement like this:
WHILE Date <29/02/2021
WHILE Date >=29/02/2021 AND Date <=03/09/2021
WHILE Date >=12/01/2022
This is my approach:
CONCATENATE(CostValue)
LOAD
Text("Placement ID") as "Placement ID",
// "Date",
Date(Date#("Date",'YYYY-MM-DD'),'DD/MM/YYYY') as Date,
(Sum(Impressions)/1000) * 4.00 as [Cost-Per-Day]
FROM [lib://Output Files/Openreach/GCM/*.qvd] (qvd)
WHERE
Match([Site (DCM)],'crimtan.com','Programmatic Ads - Crimtan Agency')
WHILE Date <29/02/2021
GROUP BY Date, "Placement ID";
CONCATENATE(CostValue)
LOAD
Text("Placement ID") as "Placement ID",
// "Date",
Date(Date#("Date",'YYYY-MM-DD'),'DD/MM/YYYY') as Date,
(Sum(Impressions)/1000) * 10.00 as [Cost-Per-Day]
FROM [lib://Output Files/Openreach/GCM/*.qvd] (qvd)
WHERE
Match([Site (DCM)],'crimtan.com','Programmatic Ads - Crimtan Agency')
WHILE Date >=29/02/2021 AND Date <=03/09/2021
GROUP BY Date, "Placement ID";
CONCATENATE(CostValue)
LOAD
Text("Placement ID") as "Placement ID",
// "Date",
Date(Date#("Date",'YYYY-MM-DD'),'DD/MM/YYYY') as Date,
(Sum(Impressions)/1000) *9.00 as [Cost-Per-Day]
FROM [lib://Output Files/Openreach/GCM/*.qvd] (qvd)
WHERE
Match([Site (DCM)],'crimtan.com','Programmatic Ads - Crimtan Agency')
WHILE Date >=12/01/2022
GROUP BY Date, "Placement ID";
However, this isn't working. The block of code I start with is fine; once I start inserting those WHILE statements with the dates, I do not get the results I expect. Please help 🙂
