Create Values that do not exist

[Anonymous]

Hello- I have a data set that is a running total of time to an event (timeOUT) which is a percent from 0 to 100%.  However, due to the number of days to each event, sometimes there will not be a value for all 101 timeOUT values that i want (see below)

teamNAME	gameDATE	timeOUT	totalPRESOLD	ticketDAYTOTAL	totalSOLD	tixSOLDPERDAY
vs. Lightning	10/10/2017 0:00	0%	10	10	1000	10
vs. Lightning	10/10/2017 0:00	5%	10	10	1000	10
vs. Lightning	10/10/2017 0:00	8%	10	25	1000	25
vs. Lightning	10/10/2017 0:00	12%	10	35	1000	35
vs. Lightning	10/10/2017 0:00	17%	10	83	1000	83
vs. Lightning	10/10/2017 0:00	20%	10	16	1000	16
vs. Lightning	10/10/2017 0:00	24%	10	12	1000	12
vs. Lightning	10/10/2017 0:00	29%	10	14	1000	14

Is there a way to create values (like 1%, 2% etc) that do not currently exist?  What i'd like to end up with is this (not concerned with filling in values for totalPRESOLD, ticketDAYTOTAL or totalSOLD:

teamNAME	gameDATE	timeOUT	totalPRESOLD	ticketDAYTOTAL	totalSOLD	tixSOLDPERDAY
vs. Lightning	10/10/2017 0:00	0%	10	10	1000	10
vs. Lightning	10/10/2017 0:00	1%				0
vs. Lightning	10/10/2017 0:00	2%				0
vs. Lightning	10/10/2017 0:00	3%				0
vs. Lightning	10/10/2017 0:00	4%				0
vs. Lightning	10/10/2017 0:00	5%	10	10	1000	10
vs. Lightning	10/10/2017 0:00	6%				0
vs. Lightning	10/10/2017 0:00	7%				0
vs. Lightning	10/10/2017 0:00	8%	10	25	1000	25

Please help!

[aarkay29 · Accepted solution]

May be this:

Table:

load * inline [

teamNAME, gameDATE, timeOUT, totalPRESOLD, ticketDAYTOTAL, totalSOLD, tixSOLDPERDAY

vs. Lightning, 10/10/2017 0:00, 0%, 10, 10, 1000, 10

vs. Lightning, 10/10/2017 0:00, 5%, 10, 10, 1000, 10

vs. Lightning, 10/10/2017 0:00, 8%, 10, 25, 1000, 25

vs. Lightning, 10/10/2017 0:00, 12%, 10, 35, 1000, 35

vs. Lightning, 10/10/2017 0:00, 17%, 10, 83, 1000, 83

vs. Lightning, 10/10/2017 0:00, 20%, 10, 16, 1000, 16

vs. Lightning, 10/10/2017 0:00, 24%, 10, 12, 1000, 12

vs. Lightning, 10/10/2017 0:00, 29%, 10, 14, 1000, 14

];

For i= 1 to 101;

Temp:

load Distinct

  teamNAME,

  gameDATE,

  $(i)-1&'%' as timeOUT

Resident

  Table;

Next i;

Join(Table)

Load *

Resident

  Temp;

Drop table

  Temp;

[Anonymous]

Unfortunately that doesn't seem to have changed the data at all, i still have the same gaps

[Anonymous]

not sure if it makes a difference, but i wanted to clarify that this is only a small section of my  data, and i have multiple dates and corresponding team names within the data set as well

[aarkay29]

PFA

Is that not what you are looking for?

[Anonymous]

that is exactly what i am looking for, and it showed up when i did a tablebox. The issue i was having was it was not showing in a pivot table, which i assume is due to suppress null on somewhere.

thank you

[Anonymous]

sorry to come back to this, but i noticed in my data set that some of the values have duplicates where there was already a value present (see below):

teamNAME	gameDATE	timeOUT	tixSOLDPERDAY
vs. Lightning	10/10/2017 0:00	80%	5
vs. Lightning	10/10/2017 0:00	81%	10
vs. Lightning	10/10/2017 0:00	82%	0
vs. Lightning	10/10/2017 0:00	82%	5
vs. Lightning	10/10/2017 0:00	83%	0
vs. Lightning	10/10/2017 0:00	83%	6
vs. Lightning	10/10/2017 0:00	84%	4
vs. Lightning	10/10/2017 0:00	85%	-32
vs. Lightning	10/10/2017 0:00	86%	4

Any idea why it would be doing this for the larger data set? Most of the time it appears there are 110 values for each team, instead of the 101 I'd expect.

Thank you!

Chris

[aarkay29]

I believe this is due to null value handling and assigning zero to null values for tixSOLDPERDAY.

Can you post the final script that you are using??

[Anonymous]

RECONFIGURED_TICKETING3:

LOAD

  teamNAME,

  gameDATE,

  timeOUT,

  totalPRESOLD,

  ticketDAYTOTAL,

  totalSOLD,

  IF(timeOUT=0, ticketDAYTOTAL+totalPRESOLD, ticketDAYTOTAL) AS tixSOLDPERDAY

RESIDENT RECONFIGURED_TICKETING2

  ORDER BY gameDATE, timeOUT ASC;

DROP TABLE RECONFIGURED_TICKETING2;

For i= 0 to 100;

Temp:

load Distinct

  teamNAME,

  gameDATE,

  $(i)&'%' as timeOUT

Resident

  RECONFIGURED_TICKETING3;

Next i;

Join(RECONFIGURED_TICKETING3)

Load *

Resident

  Temp;

Drop table

  Temp;

RECONFIGURED_TICKETING5:

noConcatenate LOAD 

teamNAME,

gameDATE,

timeOUT,

If(IsNull(tixSOLDPERDAY),0,tixSOLDPERDAY) As tixSOLDPERDAY

Resident RECONFIGURED_TICKETING3

ORDER BY gameDATE, timeOUT ASC;

Drop Table RECONFIGURED_TICKETING3;

[aarkay29]

Chris,

The script seems to be correct, however there might be some data inconsistencies which is giving you multiple lines

I would recommend you to check for the data inconsistency and accordingly change the script logic

or

if you want someone to help from the community, attach the sample application or some sample data.

Thanks,

Aar