All data in rows (help!)

[#1]

Aloha!

I'm having a hard time in dealing with a specific data type. I have a XML that when read by Qlikview it load all the data in one single column. For example:

Single Column

Name1

Address1

Profission1

Name2

Address2

Profission2

Name3

Address3

Profission3

So, I have information of many people, but all in one column. I would to like to load the data like this:

Name        Adress           Profission

Name1       Address1      Profission1

Name2       Address2      Profission2

Name3       Address3      Profission3

Name4       Address4      Profission4

Any tips?

Thnks!!

Example file attached below.

[ashfaq_haseeb]

Hi,

Can you share some sample data?

Regards

ASHFAQ

[#3]

I've just attached!

[sebastianlettner]

HI,

try this

BaseFile:
LOAD
     A,
     RowNo() as row,
     Ceil(RowNo()/3) as id
FROM
[..\..\..\Downloads\Table.xlsx]
(ooxml, no labels, table is Plan1);




Result:
LOAD
     id,
     A as Name
Resident BaseFile
Where row > 3
and mod(row, 3) = 1;

left join(Result)
LOAD
     id,
     A as Address
Resident BaseFile
Where row > 3
and mod(row, 3) = 2;

left join(Result)
LOAD
     id,
     A as Profission

Resident BaseFile
Where row > 3
and mod(row, 3) = 0;

Regards

Sebastian Lettner

[sebastianlettner · Accepted solution]

Hi,

if have made a generic solution

LET vCols=3;

BaseFile:
LOAD
    A,
    RowNo() as row,
    Ceil(RowNo()/$(vCols)) as id
FROM
[..\..\..\Downloads\Table.xlsx]
(ooxml, no labels, table is Plan1);

Result:
LOAD Distinct
    id
Resident BaseFile
Where row > $(vCols);

for i=0 to vCols - 1

    let vColName = Peek('A', i, 'BaseFile');

    left join(Result)
    LOAD
        id,
        A as $(vColName)
    Resident BaseFile
    Where row > $(vCols)
    and mod(row, $(vCols)) = $(i);


NEXT i;

vCols is the number of different columns you have in the table.

Regards

Sebastian Lettner

[#6]

Thank you very much!

[Digvijay_Singh]

QV data transformation also helps, using transpose,unwrap and then garbage row deletion. It generate scripts automatically, but may be time consuming for too many rows but it can dynamically decides the rows and columns to be transposed and unwrapped to handle changing no of fields.

LOAD Name,

     Address,

     Profission

FROM

(ooxml, embedded labels, table is Plan1, filters(

Transpose(),

Unwrap(Col, Pos(Top, 4)),

Unwrap(Col, Pos(Top, 7)),

Unwrap(Col, Pos(Top, 4)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 3))

));

[#8]

Thank you Digvijay for the tip!

[sebastianlettner]

Hi.

in there is a bug in the solution. The column name does not match the content.

I improved the solution.

LET vCols=3;

BaseFile:
LOAD
    A,
    RowNo() as row,
    Ceil(RowNo()/$(vCols)) as id,
    Mod(mod(RowNo(), $(vCols)) - 1, $(vCols)) as colId
FROM
[..\..\..\Downloads\Table.xlsx]
(ooxml, no labels, table is Plan1);

Result:
LOAD Distinct
    id
Resident BaseFile
Where row > $(vCols);

for i=0 to vCols - 1

    let vColName = Peek('A', i, 'BaseFile');

    left join(Result)
    LOAD
        id,
        A as $(vColName)
    Resident BaseFile
    Where row > $(vCols)
    and colId = $(i);


NEXT i;

Regards

Sebastian Lettner