topic Re: Load table modifying the structure in QlikView

Load table modifying the structure

Mon, 15 Jul 2013 21:26:33 GMT

I have a database in excel like this structure:

And I need to load this in a qlikview's table to stay like this:

Anyone can help me? Thanks.

Re: Load table modifying the structure

Anonymous — Tue, 16 Jul 2013 08:19:15 GMT

You can loop through each record in turn, and:

1. if the record is a date

- set variables to store the start and end date

2. if the record is not a date

- add record to a new table using the stored start and end date

To do the loop you would need to test the number of records first.

This would look something like this:

NewTable:

LOAD * INLINE [ RowKey ];

OriginalTable:

LOAD

Field1,

Field2

FROM .... your spreadsheet

LET vRowCount = NoOfRows('Register');

SET a = 0;

DO WHILE a <= (vRowCount-1)

LET LoopType = peek('Register',a,'OriginalTable');

IF LoopType = 'date' THEN

LET vStartDate = peek('Field1',a,'OriginalTable');

LET vEndDate = peek('Field2',a,'OriginalTable');

ELSE

LET vWorker = peek('Field1',a,'OriginalTable');

LET vValue = peek('Field2',a,'OriginalTable');

CONCATENATE (NewTable)

LOAD

RowNo() AS RowKey,

'$(vStartDate)' AS StartDate,

'$(vEndDate)' AS EndDate,

'$(vWorker)' AS Worker,

'$(vValue)' AS Value

AUTOGENERATE 1;

ENDIF

LET a = a + 1;

LOOP

Jonathan

Re: Load table modifying the structure

luciancotea — Tue, 16 Jul 2013 08:50:21 GMT

Table1:

LOAD Register, Field1, Field2,

if(peek(Register) <> Register and Register <> 'info', RowNo(), if(isnull(peek(ID)), 1, peek(ID)) ) as ID

FROM [Book1.xlsx] (ooxml, embedded labels, table is Sheet1);

Table2:

LOAD Field1 as Date_Start, Field2 as Date_End, ID

RESIDENT Table1 WHERE Register = 'date';

left join

LOAD ID, Field1 as Worker, num(Field2) as Value

RESIDENT Table1 WHERE Register = 'info';

drop table Table1;

drop field ID;

Re: Load table modifying the structure

Tue, 16 Jul 2013 10:31:37 GMT

Perfect Lucian Cotea, this works!! Thanks