topic Re: Add dates to a table in App Development

Add dates to a table

EvanBarrick — Thu, 27 Dec 2018 18:39:17 GMT

I have a data source that contains the following. My goal is to create a stacked bar chart that shows what type of medium was displayed on each day. The data only had start and end dates. How do I fill in the gaps?

Source	Medium	Date of Placement	End Date
SocSab	FB	23-Nov	1-DEC
SocSab	TW	23-Nov	3-DEC
SocSab	INSTAS	23-Nov	29-DEC
SocSab	SNAP	23-Nov	25-NOV
ESab	IGSAB23	26-Nov	12-JAN
ESab	IGSAB23	5-Jan	4-JAN
NonDigital	Billboard	5-Jan	12-Jan
NonDigital	Radio	1-Jan	12-Jan
NonDigital	TV	8-Jan	12-JAN

Re: Add dates to a table

sunny_talwar — Thu, 27 Dec 2018 18:47:19 GMT

The start and end dates are only made up of day and month? No information about year?

Re: Add dates to a table

EvanBarrick — Thu, 27 Dec 2018 18:48:04 GMT

Correct

Re: Add dates to a table

sunny_talwar — Thu, 27 Dec 2018 18:57:45 GMT

But then how do you determine if this has gone for a single year or multiple years?

For example from 26-Nov 2018 to 12-Jan-2019 or 12-Jan-2020? I mean there has to be some way to determine what is the duration between Start and End, right?

Re: Add dates to a table

EvanBarrick — Thu, 27 Dec 2018 18:59:55 GMT

Will work with source data to be added, can assume that going forward will be formatted as 'MM/DD/YYYY'

Re: Add dates to a table

sunny_talwar — Thu, 27 Dec 2018 19:09:02 GMT

Something like this

Table:
LOAD *,
	 Date([Date of Placement] + IterNo() - 1) as Date
While Date([Date of Placement] + IterNo() - 1) <= [End Date];
LOAD * INLINE [
    Source, Medium, Date of Placement, End Date
    SocSab, FB, 23-Nov-2018, 1-Dec-2018
    SocSab, TW, 23-Nov-2018, 3-Dec-2018
    SocSab, INSTAS, 23-Nov-2018, 29-Dec-2018
    SocSab, SNAP, 23-Nov-2018, 25-Nov-2018
    ESab, IGSAB23, 26-Nov-2018, 12-Jan-2019
    ESab, IGSAB23, 5-Jan-2019, 4-Jan-2019
    NonDigital, Billboard, 5-Jan-2019, 12-Jan-2019
    NonDigital, Radio, 1-Jan-2019, 12-Jan-2019
    NonDigital, TV, 8-Jan-2019, 12-Jan-2019
];

Re: Add dates to a table

EvanBarrick — Thu, 27 Dec 2018 19:22:36 GMT

My data source is a .csv file that I would be loading in all data (there are more columns) how would I need to alter the script

Re: Add dates to a table

sunny_talwar — Thu, 27 Dec 2018 19:23:34 GMT

Replace Inline load with CSV load 🙂