topic Re: Create Values that do not exist in QlikView

Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 14:55:18 GMT

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!

Re: Create Values that do not exist

aarkay29 — Tue, 07 Mar 2017 15:48:33 GMT

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;

Re: Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 15:53:24 GMT

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

Re: Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 15:57:11 GMT

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

Re: Create Values that do not exist

aarkay29 — Tue, 07 Mar 2017 16:08:54 GMT

PFA

Is that not what you are looking for?

Re: Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 16:13:12 GMT

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

Re: Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 20:40:54 GMT

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

Re: Create Values that do not exist

aarkay29 — Tue, 07 Mar 2017 20:47:46 GMT

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??

Re: Create Values that do not exist

Anonymous — Tue, 07 Mar 2017 20:58:56 GMT

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;

Re: Create Values that do not exist

aarkay29 — Tue, 07 Mar 2017 21:29:42 GMT

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

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

Thanks,

Aar

Re: Create Values that do not exist

Anonymous — Wed, 08 Mar 2017 14:57:10 GMT

Pleas see the sample app provided. For reference, the chart shows 4 lines. The yellow line (avg(RunningTotal)) is the line I am trying to fix with the issues we have been discussing. the green line (=SalesTrend) is a test line that i built outside of the system manually. Once the duplication issues are no more, the yellow line should match that green line.

Thank you again for your help