Forum

Kognitio Console issues, advice, etc.
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

A Transpose function

by MikeAtkinson » Mon May 16, 2016 11:55 am

This example shows how to transpose a resultset table in a similar way to the SAS TRANSPOSE function.

It does not attempt to replicate all the SAS Transpose function options, but these could be relatively easily added.

It is not recommended for lots of data, up to a few thousand rows should be OK.

First is a transpose module written in Lua (called "transpose.kog"):

Code: Select all

local trans = {}
/* A helper function to create a unique identifier out of the data in the "BY"
 * fields for a row.
 */
function trans.rowSpecifier(row, by)
    local x = {}
    for _,c in ipairs(by) do
        table.insert(x, row[c])
    end
    return table.concat(x,"#")
end

/* Transpose a resultset table.
 * NOTE: not all the SAS transpose procedure options are supported.
 */
function trans.transpose(t, by, var, id)
    local out = {}
    local unique_id = {}
    local unique_ids = {}
    local unique_by = {}
    local rows = t.rows
    
    -- find unique values in the columns specified by the "by" field
    for _,r in ipairs(rows) do
        unique_id[r[id]] = 1
        local x = {}
        for _,c in ipairs(by) do
            table.insert(x, r[c])
        end
       unique_by[trans.rowSpecifier(r,by)] = x
    end
    -- unique_by = { "1001#Grocery" => {1001,Grocery}, "1001#Beauty Care" => {1001,Beauty Care} }

    -- Get all the column headers
    newcols = by;
    for i2,v2 in pairs(unique_id) do
        table.insert(unique_ids,i2)
    end
    table.sort(unique_ids)
    for i,v in pairs(unique_ids) do
        table.insert(newcols,v)
    end
    -- newcols = {"Store,Category,Week_1,Week_2,Week_3,Week_4,Week_5,Week_6,Week_7,Week_8}
     
    for i,r in ipairs(rows) do
        local val = r[var]
        local row_id = r[id]
        unique_by[trans.rowSpecifier(r,by)][row_id] = val
    end
    newrows = {}
    for i,v in pairs(unique_by) do
        for _,v2 in pairs(unique_ids) do
            local v3 = v[v2]
            if v3 then table.insert(v, v3) else table.insert(v, "") end
        end
        table.insert(newrows,v)
    end
    out.colNames = newcols
    out.numCols  = #newcols
    out.rows     = newrows
    out.numRows  = #newrows
    return out
end

return trans;
And here is an example of how to use the transpose module:

Code: Select all

/*
This example shows how to transpose a resultset table in a similar way to the SAS TRANSPOSE function

Store   Category       Week_Number    Dollars 
1001    Grocery        Week_1         2154 
1001    Grocery        Week_2         2117 
1001    Grocery        Week_3         2147 
1001    Grocery        Week_4         2085 
1001    Grocery        Week_5         1901 
1001    Grocery        Week_6         2175
1001    Grocery        Week_7         2147
1001    Grocery        Week_8         2030 
1001    Beauty Care    Week_1         664 
1001    Beauty Care    Week_2         603 
1001    Beauty Care    Week_3         828 
1001    Beauty Care    Week_4         678 
1001    Beauty Care    Week_5         763 
1001    Beauty Care    Week_6         708 


Output_Table
Store  Category     Week_1 Week_2 Week_3 Week_4 Week_5 Week_6 Week_7 Week_8
1001   Grocery      2154   2117   2147   2085   1801   2175   2147   2030
1001   Beauty Care  664    603    828    678    763    708

For the SAS procedure:

   PROC TRANSPOSE DATA=input_table OUT=output_table;
   BY store_no category;
   VAR dollars;
   ID week_number;
   RUN;

*/

test = "transpose_test"
create schema $(test);
drop table $(test).input_table;
create table $(test).input_table (store int, category varchar(100), week_number varchar(10), dollars int);
insert into $(test).input_table values (1001, 'Grocery', 'Week_1', 2154);
insert into $(test).input_table values (1001, 'Grocery', 'Week_2', 2117);
insert into $(test).input_table values (1001, 'Grocery', 'Week_3', 2147);
insert into $(test).input_table values (1001, 'Grocery', 'Week_4', 2085);
insert into $(test).input_table values (1001, 'Grocery', 'Week_5', 1901);
insert into $(test).input_table values (1001, 'Grocery', 'Week_6', 2175);
insert into $(test).input_table values (1001, 'Grocery', 'Week_7', 2147);
insert into $(test).input_table values (1001, 'Grocery', 'Week_8', 2030);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_1', 664);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_2', 603);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_3', 828);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_4', 678);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_5', 763);
insert into $(test).input_table values (1001, 'Beauty Care', 'Week_6', 708);

-- Forget about any existing transpose package
package.loaded["transpose"] = nil
local transpose = require "transpose"

input_table = select * from $(test).input_table ;
output_table = transpose.transpose(input_table,{'STORE', 'CATEGORY'}, 'DOLLARS', 'WEEK_NUMBER')

print(table.concat(output_table.colNames,", "))
for i,v in pairs(output_table.rows) do
    print(i .. ": " .. table.concat(v,", "))
end
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron