transform data (maybe crosstable)

[#1]

I have data in following format

Store#	Year-Month	Toys	Cleaning	Pharmacy
1	2014-1	44	665	343
1	2014-2	343	6565	676
1	2014-3	35	2343	878
1	2014-4	5	3452	6565
1	2014-5	656	54542	343
1	2014-6	7	432	9896
1	2014-7	687	343	656
1	2014-8	87	343	454
1	2014-9	998	131	32423
1	2014-10	989	353	6564
1	2014-11	343	32345	565
1	2014-12	1	4	7
2	2014-1	2	5	8
2	2014-2	3	6	9
2	2014-3	4	7	10
2	2014-4	5	8	11
2	2014-5	6	9	12
2	2014-6	7	10	13
2	2014-7	8	11	14
2	2014-8	9	12	15
2	2014-9	10	13	16
2	2014-10	11	14	17
2	2014-11	12	15	18
2	2014-12	13	16	19

How can i transform it in below format

Store#	Category	2014-1	2014-2	2014-3	2014-4	2014-5	2014-6	2014-7	2014-8	2014-9	2014-10	2014-11	2014-12
1	Toys	44	343	35	5	656	7	687	87	998	989	343	1
1	Cleaning	665	6565	2343	3452	54542	432	343	343	131	353	32345	4
1	Pharmacy	343	676	878	6565	343	9896	656	454	32423	6564	565	7
2	Toys	2	3	4	5	6	7	8	9	10	11	12	13
2	Cleaning	5	6	7	8	9	10	11	12	13	14	15	16
2	Pharmacy	8	9	10	11	12	13	14	15	16	17	18	19

[Gysbert_Wassenaar · Accepted solution]

Personally, I'd stop after the Crossload and only show the data in the format you posted and not store it in that format. But that's up to you. You can use (parts of) this script:

Temp:
CrossTable(Category, Value,2)
LOAD * INLINE [
    Store#, Year-Month, Toys, Cleaning, Pharmacy
    1, 2014-1, 44, 665, 343
    1, 2014-2, 343, 6565, 676
    1, 2014-3, 35, 2343, 878
    1, 2014-4, 5, 3452, 6565
    1, 2014-5, 656, 54542, 343
    1, 2014-6, 7, 432, 9896
    1, 2014-7, 687, 343, 656
    1, 2014-8, 87, 343, 454
    1, 2014-9, 998, 131, 32423
    1, 2014-10, 989, 353, 6564
    1, 2014-11, 343, 32345, 565
    1, 2014-12, 1, 4, 7
    2, 2014-1, 2, 5, 8
    2, 2014-2, 3, 6, 9
    2, 2014-3, 4, 7, 10
    2, 2014-4, 5, 8, 11
    2, 2014-5, 6, 9, 12
    2, 2014-6, 7, 10, 13
    2, 2014-7, 8, 11, 14
    2, 2014-8, 9, 12, 15
    2, 2014-9, 10, 13, 16
    2, 2014-10, 11, 14, 17
    2, 2014-11, 12, 15, 18
    2, 2014-12, 13, 16, 19
];

Temp2:
Generic load Store#,Category, [Year-Month], Value Resident Temp;

drop Table Temp;

Result:
load 1 as Store#, 1 as Category
AutoGenerate 0;

for i = 0 to NoOfTables() -2
  let tn = TableName($(i));
  join(Result)
  load * Resident [$(tn)];
next

LET vTables = chr(34) & TableName(0) & chr(34);

FOR i = 1 to NoOfTables() - 2
    LET d = chr(34) & TableName(i) & chr(34);
    LET vTables = vTables & ', ' & d;
    TRACE $(vTables);
NEXT
DROP Tables $(vTables);

[#3]

anyone with clue

[Nicole-Smith]

Generic Load Joining

[userid128223]

How Nicole. Can you give example based on my question.

[#6]

Maybe this might help:

Row values as fields (opposite of crosstable?)

[Gysbert_Wassenaar]

Yes, but for some reason I cannot fathom my clue is is stuck in the moderation queue. You'll have to wait for a moderator to approve my post.

[felipe_dutra]

See the attachment:

[Nicole-Smith]

Load script (it will handle any number of categories and year-months):

Test:

CrossTable(Category,Value,2)

LOAD *;

LOAD * INLINE [

    Store#, Year-Month, Toys, Cleaning, Pharmacy

    1, 2014-1, 44, 665, 343

    1, 2014-2, 343, 6565, 676

    1, 2014-3, 35, 2343, 878

    1, 2014-4, 5, 3452, 6565

    1, 2014-5, 656, 54542, 343

    1, 2014-6, 7, 432, 9896

    1, 2014-7, 687, 343, 656

    1, 2014-8, 87, 343, 454

    1, 2014-9, 998, 131, 32423

    1, 2014-10, 989, 353, 6564

    1, 2014-11, 343, 32345, 565

    1, 2014-12, 1, 4, 7

    2, 2014-1, 2, 5, 8

    2, 2014-2, 3, 6, 9

    2, 2014-3, 4, 7, 10

    2, 2014-4, 5, 8, 11

    2, 2014-5, 6, 9, 12

    2, 2014-6, 7, 10, 13

    2, 2014-7, 8, 11, 14

    2, 2014-8, 9, 12, 15

    2, 2014-9, 10, 13, 16

    2, 2014-10, 11, 14, 17

    2, 2014-11, 12, 15, 18

    2, 2014-12, 13, 16, 19

];

CategoryList:

LOAD DISTINCT Category as CategoryList

RESIDENT Test

ORDER BY Category;

LET vLoop = FieldValueCount('Category');

FOR c=1 TO vLoop

    LET vCategory = PEEK('CategoryList',$(c)-1,'CategoryList');

    LET vCategoryTable = '[' & PEEK('CategoryList',$(c)-1,'CategoryList') & ']';

    $(vCategoryTable):

    Generic

    LOAD Store#, [Year-Month], Value

    Resident Test

    WHERE Category = '$(vCategory)'; 

    YearMonthList:

    LOAD DISTINCT [Year-Month] as YearMonthList

    RESIDENT Test

    ORDER BY [Year-Month] DESC;

    LET vLoop2 = FieldValueCount('YearMonthList')-1;

    LET vTable = '[$(vCategory).' & PEEK('YearMonthList',0,'YearMonthList') & ']';

    LET vYearMonth = '[' & PEEK('YearMonthList',0,'YearMonthList') & ']';

    RENAME TABLE $(vTable) TO $(vCategoryTable);

    Outer Join ($(vCategoryTable))

    LOAD *, '$(vCategory)' as Category

    RESIDENT $(vCategoryTable);

    FOR i=1 TO vLoop2

        LET vTable = '[$(vCategory).' & PEEK('YearMonthList',$(i),'YearMonthList') & ']';

        OUTER JOIN ($(vTable))

        LOAD *,'$(vCategory)' as Category

        RESIDENT $(vTable);

        OUTER JOIN ($(vCategoryTable))

        LOAD *

        RESIDENT $(vTable);

        DROP TABLE $(vTable);   

    NEXT i

    IF $(c)=1 THEN

        RENAME Table $(vCategoryTable) TO Final;

    ELSE

        CONCATENATE (Final)

        LOAD *

        RESIDENT $(vCategoryTable);

        DROP TABLE $(vCategoryTable);

    ENDIF

NEXT c

DROP TABLE CategoryList;

DROP TABLE YearMonthList;

DROP TABLE Test;

I've also attached a working file.