cross table for two column

[bilionut]

Hi everyone,

My excel looks that this one (I know, it's not the happiest structure)

ITEM_KEY	Value0	Date0	Value1	Date1	Value2	Date2	Value3	Date3	Value4	Date4	Value5	Date5
8893	2450	07-Jan-09
12318	2200	09-Nov-12			1000	25-May-13
133788	3500	20-Jun-14
135355	1830	03-Jun-16	30	08-Jul-16
135616	2000	04-Nov-16
6473	4250	31-Jan-07
11048	11950	18-May-11	2000	23-Nov-11	1800	20-Apr-12			1800	22-Mar-13	1600	29-Nov-14
11049	11550	18-May-11	2000	23-Nov-11	1800	24-Aug-12	1800	24-Aug-12	1600	06-Sep-13	1600	06-Mar-15
11607	10550	18-May-11	800	07-Sep-12	3494	14-Sep-12	1600	30-Aug-13	1600	27-Mar-15	1600	06-May-17
9464	1450	19-Aug-09
1173	2400	04-Feb-02
8137	4400	21-May-08	1200	05-Apr-13
8357	4700	30-Jul-08	1000	06-May-16
9607	5950	04-Nov-09	1600	22-Feb-13	1200	27-Feb-15	1200	16-Dec-16			44	10-Feb-17

I would be simple to arrange data with crosstable if it were a single data column but I need to get two pairs of columns

ITEM_KEY1, Value0, Date0

ITEM_KEY1, Value1, Date1

And so on reaching to Value30, Date 30

Do you have any idea how to do it?

[Anonymous]

So you need ITEM_KEY, Value, and Date columns?

[sunny_talwar · Accepted solution]

Check this

Table:

CrossTable (Desc, Value)

LOAD * INLINE [

    ITEM_KEY, Value0, Date0, Value1, Date1, Value2, Date2, Value3, Date3, Value4, Date4, Value5, Date5

    8893, 2450, 07-Jan-09,

    12318, 2200, 09-Nov-12, , , 1000, 25-May-13

    133788, 3500, 20-Jun-14

    135355, 1830, 03-Jun-16, 30, 08-Jul-16

    135616, 2000, 04-Nov-16

    6473, 4250, 31-Jan-07

    11048, 11950, 18-May-11, 2000, 23-Nov-11, 1800, 20-Apr-12, , , 1800, 22-Mar-13, 1600, 29-Nov-14

    11049, 11550, 18-May-11, 2000, 23-Nov-11, 1800, 24-Aug-12, 1800, 24-Aug-12, 1600, 06-Sep-13, 1600, 06-Mar-15

    11607, 10550, 18-May-11, 800, 07-Sep-12, 3494, 14-Sep-12, 1600, 30-Aug-13, 1600, 27-Mar-15, 1600, 06-May-17

    9464, 1450, 19-Aug-09

    1173, 2400, 04-Feb-02

    8137, 4400, 21-May-08, 1200, 05-Apr-13, , , ,

    8357, 4700, 30-Jul-08, 1000, 06-May-16, , , ,

    9607, 5950, 04-Nov-09, 1600, 22-Feb-13, 1200, 27-Feb-15, 1200, 16-Dec-16, , , 44, 10-Feb-17

];

FinalTable:

LOAD ITEM_KEY,

  KeepChar(Desc, '0123456789') as Num,

  Date#(Value, 'DD-MMM-YY') as Date

Resident Table

Where Len(Trim(Value)) > 0 and WildMatch(Desc, 'Date*');

Left Join(FinalTable)

LOAD ITEM_KEY,

  KeepChar(Desc, '0123456789') as Num,

  Value as Value

Resident Table

Where Len(Trim(Value)) > 0 and WildMatch(Desc, 'Value*');

DROP Table Table;

[MarcoWedel]

Hi,

one solution might be also:

tabTemp:
CrossTable (ColNam, ColVal)
LOAD * FROM [https://community.qlik.com/thread/265326] (html, codepage is 1252, embedded labels, table is @1);

table1:
Generic
LOAD ITEM_KEY,
    KeepChar(ColNam,'0123456789') as GroupNo,
    PurgeChar(ColNam,'0123456789'),
    ColVal
Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco