topic Re: Unwrap in QlikView

Unwrap

Mon, 09 May 2016 09:07:24 GMT

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

Re: Unwrap

Peter_Cammaert — Mon, 09 May 2016 09:21:45 GMT

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

Re: Unwrap

Mon, 09 May 2016 09:40:15 GMT

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. 😞

Re: Unwrap

swuehl — Mon, 09 May 2016 09:45:04 GMT

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;

Re: Unwrap

Peter_Cammaert — Mon, 09 May 2016 09:54:03 GMT

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

Re: Unwrap

mightyqlikers — Mon, 09 May 2016 09:58:32 GMT

please share sample excel.

$@M.