topic Re: All data in rows (help!) in QlikView

All data in rows (help!)

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: All data in rows (help!)

ashfaq_haseeb — Thu, 15 Oct 2015 11:56:09 GMT

Hi,

Can you share some sample data?

Regards

ASHFAQ

Re: All data in rows (help!)

Thu, 15 Oct 2015 12:01:42 GMT

I've just attached!

Re: All data in rows (help!)

sebastianlettner — Thu, 15 Oct 2015 12:37:29 GMT

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

Re: All data in rows (help!)

sebastianlettner — Thu, 15 Oct 2015 12:49:01 GMT

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

Re: All data in rows (help!)

Thu, 15 Oct 2015 12:53:50 GMT

Thank you very much!

Re: All data in rows (help!)

Digvijay_Singh — Thu, 15 Oct 2015 12:59:24 GMT

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))

));

Re: All data in rows (help!)

Thu, 15 Oct 2015 13:03:45 GMT

Thank you Digvijay for the tip!

Re: All data in rows (help!)

sebastianlettner — Thu, 15 Oct 2015 13:04:57 GMT

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