Unwrap

[#1]

Good day,

I hope you can help me.

I am trying to unwrap an excel sheet but struggling.

The data currently looks like this, the numbers is "No of Sessions"

ColumnA     ColumnB     ColumnC     2016-04-09     2016-04-10     2016-04-11     2016-04-12     ColumnD

AAAA          Test Data   Apple          101               105                107                110                (Account Name)

BBBB          Test Data   Blackberry   103               109                150                153                (Account Name)

How I need this data is in the following format.

ColumnD     ColumnB     Date            No of Sessions

Please help me to get this end result as I have tried using the unwrap feature in QlikView but can't get the require result.

Thanks,

Marius

[Peter_Cammaert]

I'm assuming that there will be a varying number of date columns in this Excel? Which makes it a bit more difficult to use a CROSSTABLE LOAD with an asterisk as column specifier.

The easiest soution would be to change the layout of the source Excel and move Column D to the area before the date columns. That would allow for an extremely simple load script. Can you use all you persuasive powers to get this small modification implemented?

Peter

[#3]

Thanks for the reply Peter. The problem that I have is that this is actually an external API that we are buying that generates this spreadsheet which makes it impossible to change the fields around. 😞

[swuehl · Accepted solution]

Maybe something like

INPUT:

CROSSTABLE (Date, [No of Sessions],4)

LOAD ColumnD as NewColumnD, *

FROM YourExcelFile;

RESULT:

NOCONCATENATE LOAD

     NewColumnD as ColumnD,

     ColumnB,

     Date(Num#(Date)) as Date, //assuming CROSSTABLE has loaded your date coumn headers as text number

     [No of Sessions]

RESIDENT INPUT

WHERE Date <> 'ColumnD';

DROP TABLE INPUT;

[Peter_Cammaert]

Dropped my reply. Stefan's solution is way simpler.

[mightyqlikers]

please share sample excel.

$@M.