18 Replies Latest reply: Dec 21, 2015 8:13 AM by Marcus Sommer RSS

    Creation of line chart using cross tables

    Andrew Thomas

      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

        • Re: Creation of line chart using cross tables
          Marcus Sommer

          I think you should transform your table with The Crosstable Load

           

          - Marcus

            • Re: Creation of line chart using cross tables
              Andrew Thomas

              I have used the crosstable load. Its the next bit I'm stuck on

               

              How to create the variance calc from the 2 fields and accumulate the values in the line chart

                • Re: Creation of line chart using cross tables
                  Marcus Sommer

                  If you load those tables with an approach like this one: Creation of Line chart unless you used now value1 and value2 instead of value and you could in a following load join both tables and then you havbe both values within one table-row and could calculate with them.

                   

                  - Marcus

                    • Re: Creation of line chart using cross tables
                      Andrew Thomas

                      Any chance you could demo in the script for me

                       

                      Do you mean the COSP load  and COSS load should both use value or do you been they should be value1 and value 2

                        • Re: Creation of line chart using cross tables
                          Marcus Sommer

                          Try something like this:

                           

                          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 [Costing Table COSP];

                           

                          t2:

                          CrossTable(Periode, Value2)

                          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 [Costing Table COSS];

                           

                          t3:

                          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

                            • Re: Creation of line chart using cross tables
                              Andrew Thomas

                              Hi

                               

                              Almost there...but it doesn't like the Join in T3 ? The load just stops...any other ideas ?

                                • Re: Creation of line chart using cross tables
                                  Marcus Sommer

                                  Try:

                                   

                                  t3:

                                  Noconcatenate Load Periode, Value1 Resident t1;

                                       join

                                  Load Periode, Value2 Resident t2;

                                   

                                  - Marcus

                                    • Re: Creation of line chart using cross tables
                                      Andrew Thomas

                                      Hi

                                       

                                      No it still just hangs and won't load

                                        • Re: Creation of line chart using cross tables
                                          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

                                            • Re: Creation of line chart using cross tables
                                              Andrew Thomas

                                              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

                                                • Re: Creation of line chart using cross tables
                                                  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

                                                    • Re: Creation of line chart using cross tables
                                                      Andrew Thomas

                                                      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

                                                      [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\CostingTableCOSP.QVD]

                                                      (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

                                                      [D:\SourceDocuments\PRODQLIK\DATA\SAP DATA\CostingTableCOSS.QVD]

                                                      (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;

                                                        • Re: Creation of line chart using cross tables
                                                          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

                                                            • Re: Creation of line chart using cross tables
                                                              Andrew Thomas

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

                                                                • Re: Creation of line chart using cross tables
                                                                  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

                                                                  [D:\CostingTableCOSP.QVD]

                                                                  (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

                                                                  [D:\CostingTableCOSP.QVD]

                                                                  (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

                                          • Re: Creation of line chart using cross tables
                                            sasi k

                                            HI,

                                            try once

                                            Create two flags

                                            Load * ,'P1' as Flag FROM GCVO;

                                            concatinate

                                            LOAD *, 'P2' as Flag FROM COSS;

                                             

                                            Crate a line chart with exp:

                                            (sum({<Flag={'P1'}>} value)-sum({Flag={'P2'}>}value)) / Sum({<Flag={'P1'}>}value)

                                             

                                            for accumulation you can select the same option that is available in Expression tab

                                            or else

                                            You can use RangeSum() function for the same

                                        • Re: Creation of line chart using cross tables
                                          Andrew Thomas

                                          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