0 Replies Latest reply on Oct 16, 2013 10:34 AM by Marc Fernandes

    Need help with a SQL query to create dummy data

    Marc Fernandes

      I'm trying to create a dummy data source to use to help include zero counts for instances where there are no records.  I've looked at various data padding threads which have helped, but have not been able to fully solve my issue.  I'm hoping there's a way to write a SQL query to make this work.  I've been able to make things work in Excel by starting to create a dummy data set (see 'Dummy Data' on attached workbook) but I'm trying to add further levels of detail ('Division' and 'Location'), which complicates things (see 'All Possible Choices' sheet).  I'm already at 35,056 rows of data and adding the next level of detail will, by my calculations, shoot me up to 736,176 rows, which is going to be a pain to manage manually, should things change.  Adding another challenge is the fact that in my data, 'Location' is dependent on 'Division', so I don't want to mix up 'Divisions' that don't match to some 'Locations'.  Essentially what I want to accomplish is a dummy table that contains all 'Divisions' with corresponding 'Locations' matched up with all possible 'Primary Incident Codes' for all possible 'Dates'.  I'm hoping all I'll then have to do is modify the data in 'All Possible Choices' and use a SQL query to create the dummy data in Tableau, rather than doing it in Excel.  I've looked into writing cross joins but can't seem to make it work. Any ideas would be greatly appreciated!