5 Replies Latest reply: Jan 22, 2018 12:02 PM by Livio Sebastiano Colombo RSS

    Crosstable Qlik Sense

    Livio Sebastiano Colombo

      Hi Everyone,

      I have a question. I have this table:

       

      [TotC8]:

      LOAD

      [@1] AS [TotC8.Scenario],

      [@2] AS [TotC8.Year],

      [@3] AS [TotC8.Period],

      [@5] AS [TotC8.Brand],

      [@7] AS [TotC8.Account],

      [@10] AS [TotC8.Market],

      [@16] AS [TotC8.BusinessOrg],

      [@18] AS [TotC8.HFMCode],

      [@19] AS [TotC8.Amount]

      FROM [lib://Desktop/TotC8.dat]

      (txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);

       

       

      and I want have in output a table where I have all the columns without [@7] and [@19], because I want that the column [@7] is the filed and [@19] is the value of the filed.

       

      How I can do it by using crosstable??

       

      Thanks,

      Livio

        • Re: Crosstable Qlik Sense
          Clever Anjos

          Please try with

           

          crosstable (Account,Ammount,7)

          LOAD

          [@1] AS [TotC8.Scenario],

          [@2] AS [TotC8.Year],

          [@3] AS [TotC8.Period],

          [@5] AS [TotC8.Brand],

          [@10] AS [TotC8.Market],

          [@16] AS [TotC8.BusinessOrg],

          [@18] AS [TotC8.HFMCode],

          [@7] AS [TotC8.Account],

          [@19] AS [TotC8.Amount]

          FROM [lib://Desktop/TotC8.dat]

          (txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);

          • Re: Crosstable Qlik Sense
            kaan erisen

            Hi,

             

            I am not sure if it works without seeing your data and data model. But you can try this.

             

            [Temp_TotC8]:

            LOAD

            [@1] AS [TotC8.Scenario],

            [@2] AS [TotC8.Year],

            [@3] AS [TotC8.Period],

            [@5] AS [TotC8.Brand],

            [@7] AS [TotC8.Account],

            [@10] AS [TotC8.Market],

            [@16] AS [TotC8.BusinessOrg],

            [@18] AS [TotC8.HFMCode],

            [@19] AS [TotC8.Amount]

            FROM [lib://Desktop/TotC8.dat]

            (txt, codepage is 28592, no labels, delimiter is ';', msq, header is 1 lines);

             

            temp_AccList:

            load distinct [TotC8.Account] as AccList Resident Temp_TotC8;

             

            Main:

            load distinct

            [TotC8.Scenario],

            [TotC8.Year],

            [TotC8.Period],

            [TotC8.Brand],

            [TotC8.Market],

            [TotC8.BusinessOrg],

            [TotC8.HFMCode]

            Resident Temp_TotC8;

             

             

            FOR Each a in FieldValueList('AccList')

             

              left join(Main)

              load

            [TotC8.Scenario],

            [TotC8.Year],

            [TotC8.Period],

            [TotC8.Brand],

            [TotC8.Market],

            [TotC8.BusinessOrg],

            [TotC8.HFMCode],

            TotC8.Amount as '$(a)'

              Resident Temp_TotC8

              where TotC8.Account='$(a)';

             

            NEXT a;

             

            Drop Tables Temp_TotC8,temp_AccList;

              • Re: Crosstable Qlik Sense
                Livio Sebastiano Colombo

                Hi,

                Thanks it is correct. But now I have another problem:

                 

                I have the values of the Online Org Orgs, but each PIPPO is associated with one or more ACCOUNTS. I would like to report the accounts in the column and associate the reference BusinessOrg to each one.

                 

                For example:

                Capture.JPG

                and I want 2 columns like : STA1000_TotC8 and STA1000_BOPE.

                 

                 

                Thanks