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 🙂
