1 Reply Latest reply: Nov 13, 2013 7:58 AM by Mark Casselman RSS

    Crosstable insideout

    Michael Ionkin

      Hello community

       

      QlikView_Forum.png

       

      I want to do the opposite of what the crosstable is doing.

      In 'lTolTypRef' there are only three possible values 10 ; 20 ; 30. These values I want to have as columns:

      Is it possible??

       

      Cheers chesterluck

       

      lJoblTolRef102030
      1431438225T-WA-
      14314314461-T-WZBAU
      14314317371--Halle D
        • Re: Crosstable insideout
          Mark Casselman

          Hi chesterluck,

           

          I was looking for a similar solution on the discussions and found that GENERIC tables are the solution.

          Check blog : http://qlikviewnotes.blogspot.co.uk/2010/05/use-cases-for-generic-load.html

          @

          Rewritten for your use (i hope there are no typo's):

           

          // Make a generic table for every case

          Generic_Tables:

          GENERIC LOAD IJob, IToIRef, 'REF_' & IToITypRef, sDepot

          RESIDENT L9;

           

          //list all jobs

          Jobs:

          LOAD Distinct IJob

          resident YourTableName;

           

          drop tables YourTableName;

           

          // join all generic tables

           

          FOR i = 0 to NoOfTables()

            TableList:

            LOAD TableName($(i)) as Tablename AUTOGENERATE 1

            WHERE WildMatch(TableName($(i)), 'Generic_Tables.*');

          NEXT i

           

          FOR i = 1 to FieldValueCount('Tablename')

            LET vTable = FieldValue('Tablename', $(i));

            LEFT JOIN (Klanten) LOAD * RESIDENT $(vTable);

            DROP TABLE $(vTable);

          NEXT i

           

          DROP TABLE TableList;

           

          Hope this works...