topic Re: How to handle data with self repeating columns with different name in rows ? in QlikView

How to handle data with self repeating columns with different name in rows ?

shekhar_analyti — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

How to handle data with self repeating columns with different name in rows ?

Thanks & Regards

Shekar

avinashelite — Thu, 16 Mar 2017 11:48:26 GMT

What is the output your excepting ?

for the first column

PurgeChar(field_name,'0123456789') as New_field_name

sunny_talwar — Thu, 16 Mar 2017 11:48:47 GMT

May be like this

Table:

LOAD A,

PurgeChar(A, '0123456789') as NewA,

KeepChar(A, '0123456789') as SNo

FROM

[..\..\Downloads\self repeating.xlsx]

(ooxml, no labels, table is Sheet1);

FinalTable:

LOAD DISTINCT SNo

Resident Table;

FOR i = 1 to FieldValueCount('NewA')

LET vField = FieldValue('NewA', $(i));

Left Join (FinalTable)

LOAD SNo,

B as [$(vField)]

Resident Table

Where NewA = '$(vField)';

NEXT

DROP Table Table;

shekhar_analyti — Thu, 16 Mar 2017 11:53:02 GMT

Hi Sunny ,

I guess , age is not coming properly !

sunny_talwar — Thu, 16 Mar 2017 11:55:15 GMT

It is a matter of fixing the format, underlying value isn't incorrect

shekhar_analyti — Thu, 16 Mar 2017 12:00:33 GMT

As always .. Take a bow !!