Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bilionut
Contributor III
Contributor III

cross table for two column

Hi everyone,

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

 

ITEM_KEYValue0Date0Value1Date1Value2Date2Value3Date3Value4Date4Value5Date5
8893245007-Jan-09



12318220009-Nov-12

100025-May-13
133788350020-Jun-14

135355183003-Jun-163008-Jul-16
135616200004-Nov-16









6473425031-Jan-07









110481195018-May-11200023-Nov-11180020-Apr-12

180022-Mar-13160029-Nov-14
110491155018-May-11200023-Nov-11180024-Aug-12180024-Aug-12160006-Sep-13160006-Mar-15
116071055018-May-1180007-Sep-12349414-Sep-12160030-Aug-13160027-Mar-15160006-May-17
9464145019-Aug-09





1173240004-Feb-02









8137440021-May-08120005-Apr-13
8357470030-Jul-08100006-May-16
9607595004-Nov-09160022-Feb-13120027-Feb-15120016-Dec-16

4410-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?

1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

3 Replies
kellymcgrady
Creator
Creator

So you need ITEM_KEY, Value, and Date columns?

sunny_talwar

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;

Capture.PNG

View solution in original post

MarcoWedel

Hi,

one solution might be also:

QlikCommunity_Thread_265326_Pic1.JPG

QlikCommunity_Thread_265326_Pic2.JPG

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