5 Replies Latest reply: Jan 27, 2017 8:46 AM by Anna Pochyla RSS

    How do you unpivot a loaded data table?

    Jes Lee

      I'm trying to "unpivot" my data table so that I could collapse my fields into a single dimension.   However, when I go to the data manager, the edit icon is not available, and when I click on the generated table, and Qlik tells me that the able is added using the data load script, and can only be viewed in the data model viewer after the data is loaded.

       

        • Re: How do you unpivot a loaded data table?
          Jes Lee

          I actually tried this script, but I get the following error message -- I'm not sure what I'm missing.  I'm following some of the previous posts, but I'm guessing it's the structure of my loaded data table.  It would be great if someone could help me.

           

          Thank you,

          Jessica

           

          BLS_data_1:
          LOAD
              series_id,
              "year",
              period,
              value,
              Date(Makedate([year],right([period],2)), 'M/D/YYYY') as DATE,
              Date(Makedate("year",right(period,2)), 'MMMYYYY') as MONTHYEAR,
              If(series_id='SMS01000000000000001', value) as [Alabama],
              If(series_id='SMS02000000000000001', value) as [Alaska],
              If(series_id='SMS04000000000000001', value) as [Arizona],
              If(series_id='SMS05000000000000001', value) as [Arkansas],
              If(series_id='SMS06000000000000001', value) as [California],
              If(series_id='SMS08000000000000001', value) as [Colorado],
              If(series_id='SMS09000000000000001', value) as [Connecticut],
              If(series_id='SMS10000000000000001', value) as [Delaware],
              If(series_id='SMS11000000000000001', value) as [District of Columbia],
              If(series_id='SMS12000000000000001', value) as [Florida],
              If(series_id='SMS13000000000000001', value) as [Georgia],
              If(series_id='SMS15000000000000001', value) as [Hawaii],
              If(series_id='SMS16000000000000001', value) as [Idaho],
              If(series_id='SMS17000000000000001', value) as [Illinois],
              If(series_id='SMS18000000000000001', value) as [Indiana],
              If(series_id='SMS19000000000000001', value) as [Iowa],
              If(series_id='SMS20000000000000001', value) as [Kansas],
              If(series_id='SMS21000000000000001', value) as [Kentucky],
              If(series_id='SMS22000000000000001', value) as [Louisiana],
              If(series_id='SMS23000000000000001', value) as [Maine],
              If(series_id='SMS24000000000000001', value) as [Maryland],
              If(series_id='SMS25000000000000001', value) as [Massachusetts],
              If(series_id='SMS26000000000000001', value) as [Michigan],
              If(series_id='SMS27000000000000001', value) as [Minnesota],
              If(series_id='SMS28000000000000001', value) as [Mississippi],
              If(series_id='SMS29000000000000001', value) as [Missouri],
              If(series_id='SMS30000000000000001', value) as [Montana],
              If(series_id='SMS31000000000000001', value) as [Nebraska],
              If(series_id='SMS32000000000000001', value) as [Nevada],
              If(series_id='SMS33000000000000001', value) as [New Hampshire],
              If(series_id='SMS34000000000000001', value) as [New Jersey],
              If(series_id='SMS35000000000000001', value) as [New Mexico],
              If(series_id='SMS36000000000000001', value) as [New York],
              If(series_id='SMS37000000000000001', value) as [North Carolina],
              If(series_id='SMS38000000000000001', value) as [North Dakota],
              If(series_id='SMS39000000000000001', value) as [Ohio],
              If(series_id='SMS40000000000000001', value) as [Oklahoma],
              If(series_id='SMS41000000000000001', value) as [Oregon],
              If(series_id='SMS42000000000000001', value) as [Pennsylvania],
              If(series_id='SMS44000000000000001', value) as [Rhode Island],
              If(series_id='SMS45000000000000001', value) as [South Carolina],
              If(series_id='SMS46000000000000001', value) as [South Dakota],
              If(series_id='SMS47000000000000001', value) as [Tennessee],
              If(series_id='SMS48000000000000001', value) as [Texas],
              If(series_id='SMS49000000000000001', value) as [Utah],
              If(series_id='SMS50000000000000001', value) as [Vermont],
              If(series_id='SMS51000000000000001', value) as [Virginia],
              If(series_id='SMS53000000000000001', value) as [Washington],
              If(series_id='SMS54000000000000001', value) as [West Virginia],
              If(series_id='SMS55000000000000001', value) as [Wisconsin],
              If(series_id='SMS56000000000000001', value) as [Wyoming],
              If(series_id='SMS72000000000000001', value) as [Puerto Rico],
              If(series_id='SMS78000000000000001', value) as [Virgin Islands]
          FROM [lib://BLS Employment Series AllData]
          (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

           

          BLS_data:
          LOAD
             DATE,
             MONTHYEAR,
              SUM([Alabama]) as [Alabama_NF],
              SUM([Alaska]) as [Alaska_NF],
              SUM([Arizona]) as [Arizona_NF],
              SUM([Arkansas]) as [Arkansas_NF],
              SUM([California]) as [California_NF],
              SUM([Colorado]) as [Colorado_NF],
              SUM([Connecticut]) as [Connecticut_NF],
              SUM([Delaware]) as [Delaware_NF],
              SUM([District of Columbia]) as [District of Columbia_NF],
              SUM([Florida]) as [Florida_NF],
              SUM([Georgia]) as [Georgia_NF],
              SUM([Hawaii]) as [Hawaii_NF],
              SUM([Idaho]) as [Idaho_NF],
              SUM([Illinois]) as [Illinois_NF],
              SUM([Indiana]) as [Indiana_NF],
              SUM([Iowa]) as [Iowa_NF],
              SUM([Kansas]) as [Kansas_NF],
              SUM([Kentucky]) as [Kentucky_NF],
              SUM([Louisiana]) as [Louisiana_NF],
              SUM([Maine]) as [Maine_NF],
              SUM([Maryland]) as [Maryland_NF],
              SUM([Massachusetts]) as [Massachusetts_NF],
              SUM([Michigan]) as [Michigan_NF],
              SUM([Minnesota]) as [Minnesota_NF],
              SUM([Mississippi]) as [Mississippi_NF],
              SUM([Missouri]) as [Missouri_NF],
              SUM([Montana]) as [Montana_NF],
              SUM([Nebraska]) as [Nebraska_NF],
              SUM([Nevada]) as [Nevada_NF],
              SUM([New Hampshire]) as [New Hampshire_NF],
              SUM([New Jersey]) as [New Jersey_NF],
              SUM([New Mexico]) as [New Mexico_NF],
              SUM([New York]) as [New York_NF],
              SUM([North Carolina]) as [North Carolina_NF],
              SUM([North Dakota]) as [North Dakota_NF],
              SUM([Ohio]) as [Ohio_NF],
              SUM([Oklahoma]) as [Oklahoma_NF],
              SUM([Oregon]) as [Oregon_NF],
              SUM([Pennsylvania]) as [Pennsylvania_NF],
              SUM([Rhode Island]) as [Rhode Island_NF],
              SUM([South Carolina]) as [South Carolina_NF],
              SUM([South Dakota]) as [South Dakota_NF],
              SUM([Tennessee]) as [Tennessee_NF],
              SUM([Texas]) as [Texas_NF],
              SUM([Utah]) as [Utah_NF],
              SUM([Vermont]) as [Vermont_NF],
              SUM([Virginia]) as [Virginia_NF],
              SUM([Washington]) as [Washington_NF],
              SUM([West Virginia]) as [West Virginia_NF] ,
              SUM([Wisconsin]) as [Wisconsin_NF],
              SUM([Wyoming]) as [Wyoming_NF],
              SUM([Puerto Rico]) as [Puerto Rico_NF],
              SUM([Virgin Islands]) as [Virgin Islands_NF]
          Resident BLS_data_1
          Group by MONTHYEAR, DATE;
          Drop Table BLS_data_1;

           

           

          CrossTable_1:
          CrossTable(State, BLS_employ, 2)
          Load *
          From BLS_data;


           

           

           

            • Re: How do you unpivot a loaded data table?
              Rohit Kumar

              Hi Jessica ,

               

              You are using scripting mode for loading the data and where you are pointing out , that Preparation Data works for non scripting mode i.e drag and drop a file or go to data manager and add file and it will ask for prepare data.

               

              My suggestion is , Please use cross table function to do it . It will solve your case.

               

              Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

               

               

              Rohit Kumar

              • Re: How do you unpivot a loaded data table?
                Anna Pochyla

                Try usin

                noconcatenate command before loading BLS_data.

                It will look like this:

                 

                NoConcatenate

                BLS_data:

                LOAD ...

                 

                 

                Anna

              • Re: How do you unpivot a loaded data table?
                Shraddha Gajare

                Try Loading BLS Data using data manager then only you will get unpivot option there.

                • Re: How do you unpivot a loaded data table?
                  Vineeth Pujari

                  Can you post a sample with dummy data ?