Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need some help finishing a line chart.
I have 2 data loads that effectively contain the same data
Costing Table COSP:
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
Costing Table COSS:
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
Each field contains a value for a period. I need to calculate the difference between the fields and calculate a variance % which will be dsplayed in the line chart, I also need to accumulate the values for each period. At he moment I'm trying to use the cross table function so that I can get the periods along the bottom of the chart and the values on the line.
Example
(P1PCGCV0 - P1GCV1) / P1PCGCV0) = Var %
The add P1 and P2 for the P2 point on the chart
I've attached a sample QVD wit the data in. I'm really struggling to get this done any help would be appreciated
What is the error-message? What shows the log-file? If you comment the load for t3 and t4 - how looked the table-viewer?
Another question - are there many values available for each single periode? If this is the case the join would cause a carthesic product between them - solutions could be to add further identifier-fields - ID's - to your data (datas without any categories (unless your periode) is quite strange) or to aggregate these data before the join is taking place.
- Marcus
Hi The data loads to t3, then loads from resident 1 and resident 2 but then just hangs and wont load t4. I tried it without t4 but got the same result.
Each field will have 1 value in it
Please post the script and how the table-viewer looks like after loading t2 - you could insert a exit script; to finish the script at this position.
- Marcus
Hi
The script is below and as you can see t4 is currently commented out. The table viewer shows t2 linked to t3
t1:
CrossTable(Periode, Value1)
LOAD
'Dummy' as Dummy,
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0,
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM
(qvd);
t2:
CrossTable(Periode, Value2)
LOAD
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0,
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM
(qvd);
t3:
Noconcatenate Load Periode, Value1 Resident t1;
join
Load Periode, Value2 Resident t2;
//t4:
//
//Load *, Value1 - Value2 as ValueDiff Resident t3;
drop tables t1, t2, t3;
One reason could be that t1 and t2 will be automatically concatenated - I have thought value1 and value2 would be enough to make both tables different. Now change 'Dummy' as Dummy into 'Dummy' as Dummy1 and 'Dummy' as Dummy2 (is missing within the second load). If this not worked please post a screenshot from table-viewer before t3 will be loaded.
- Marcus
That still didn't work , I can't seem to paste a screen shot of the table viewer I'll send it to you on e.mail and attach the data perhaps you could see what the issue is from there
Hi I sent an e.mail but got an out of office for you, were you able to pick it up
You haven't only one value per Periode and therefore the join will create a carthesic product (each against each) and this could continue for hours probably until you haven't any RAM anymore. If you aggregate at first those tables and join it then it will work (see below - I used the Cosp-table twice because I hadn't the Coss-table within the original).
But I'm not sure if the result is what you expect - then your sources are real SAP tables with a lot more fields and I think to load only these periode-fields is really error-prone and I mean not the technically side more the logically.
I suggest that you rethink your datamodel approach carefully and maybe this is further helpful: Get started with developing qlik datamodels
t1:
CrossTable(Periode, Value1)
LOAD
'Dummy' as Dummy1,
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0,
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM
(qvd);
t2:
CrossTable(Periode, Value2)
LOAD
'Dummy' as Dummy2,
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0,
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM
(qvd);
t3:
NoConcatenate Load Periode, sum(Value1) as Value1 Resident t1 Group By Periode;
t4:
NoConcatenate Load Periode, sum(Value2) as Value2 Resident t2 Group By Periode;
t5:
Noconcatenate Load Periode, Value1 Resident t3;
join
Load Periode, Value2 Resident t4;
t6:
Load *, Value1 - Value2 as ValueDiff Resident t5;
drop tables t1, t2, t3, t4, t5;
- Marcus
If you enabled the extended editor you could attach your files.
- Marcus