Creation of line chart using cross tables

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer]

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

[#5]

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;

[marcus_sommer]

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

[#7]

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

[#8]

Hi I sent an e.mail but got an out of office for you, were you able to pick it up

[marcus_sommer]

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

[marcus_sommer]

If you enabled the extended editor you could attach your files.

- Marcus