topic Re: Trim spaces between data in qlikview in QlikView

Trim spaces between data in qlikview

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

I have data as below

ID Status

1 Active : Yes

InActive: No

Expired : No

2 Active : NO

InActive : Yes

Expired :Yes

I want to make it as

ID Active Inactive Expired

1 Yes No No

How can I make it ?

Thanks much

Re: Trim spaces between data in qlikview

sunny_talwar — Tue, 11 Oct 2016 15:51:28 GMT

Check this out:

The Generic Load

Re: Trim spaces between data in qlikview

apthansh — Tue, 11 Oct 2016 15:55:53 GMT

Thank you Sunny....but do you have a simpler example on this ?

Re: Trim spaces between data in qlikview

sunny_talwar — Tue, 11 Oct 2016 15:56:25 GMT

Or this:

Table:

LOAD ID,

SubField(Status, ' : ', 1) as Status,

SubField(Status, ' : ', 2) as Value;

LOAD * INLINE [

ID, Status

1, Active : Yes

1, InActive : No

1, Expired : No

2, Active : NO

2, InActive : Yes

2, Expired : Yes

];

FinalTable:

LOAD Distinct ID

Resident Table;

For i = 1 to FieldValueCount('Status')

LET vStatus = FieldValue('Status', $(i));

Left Join (FinalTable)

LOAD ID,

Value as $(vStatus)

Resident Table

Where Status = '$(vStatus)';

NEXT

DROP Table Table;

Re: Trim spaces between data in qlikview

julian_rodriguez — Tue, 11 Oct 2016 15:59:44 GMT

Hello Ansh

This can work if the data has always the same structure:

Data:

LOAD Id,

Status

FROM

[Libro1.xlsx]

(ooxml, embedded labels, table is Hoja1);

Detail:

LOAD

Id,

Trim(Mid(Status,Index(Status,':',1)+1, Index(Status,Chr(10),1))) As Active,

Trim(Mid(Status,Index(Status,':',2)+1, Index(Status,Chr(10),2))) As InActive,

Trim(Mid(Status,Index(Status,':',3)+1, Len(Status))) As Expired

Resident Data;

Please see attachment for details

Regards

Julian

Re: Trim spaces between data in qlikview

apthansh — Tue, 11 Oct 2016 16:27:25 GMT

Hi Julian ...that kind of worked for me...but for few ID's I have another status associated...how does that work then ?

ex:

ID Status

1 Active : Yes

InActive: No

Expired : No

2 Active : NO

InActive : Yes

Expired :Yes

Valid : Yes

julianrodriguez2‌

Re: Trim spaces between data in qlikview

antoniotiman — Tue, 11 Oct 2016 17:30:47 GMT

Hi,

Load * Where Len(Trim(Status)) > 0;
LOAD ID,SubField(Status,':',1) as Status,SubField(Status,':',-1) as Value;
Load ID,SubField(Replace(Replace(Status,' :',':'),': ',':'),' ') as Status Inline [
ID, Status
1 , 'Active : Yes InActive: No Expired : No'
2 , 'Active : NO InActive : Yes Expired :Yes Valid : Yes'
];

or, if You add :

Generic LOAD * Resident Temp;
Drop Table Temp;

Regards,

Antonio

Re: Trim spaces between data in qlikview

stevedark — Tue, 11 Oct 2016 17:46:59 GMT

This will do it for you:

Temp_Table:

LOAD

if(len(ID) = 0, peek(ID, -1), ID) as ID,

Status

INLINE [

ID,Status

1,Active : Yes

,InActive: No

,Expired : No

2,Active : NO,

,InActive : Yes

,Expired :Yes

]

;

Table:

LOAD

ID,

maxstring(if(trim(upper(SubField(Status,':',1))) = 'ACTIVE', Capitalize(trim(SubField(Status, ':', 2))))) as Active,

maxstring(if(trim(upper(SubField(Status,':',1))) = 'INACTIVE', Capitalize(trim(SubField(Status, ':', 2))))) as Inactive,

maxstring(if(trim(upper(SubField(Status,':',1))) = 'EXPIRED', Capitalize(trim(SubField(Status, ':', 2))))) as Expired

RESIDENT Temp_Table

GROUP BY ID

;

DROP TABLE Temp_Table;

The if(len(ID) = 0, peek(ID, -1), ID) as ID, code will populate the missing ID's all the way down the table. Once that is on you can flatten with a GROUP BY and work things into columns.

It relies on you knowing which fields you are expecting though?

Steve

Re: Trim spaces between data in qlikview

DavidFoster1 — Wed, 12 Oct 2016 09:32:36 GMT

Another approach.

Use the file transformation wizard to fill in the missing ID values (you can fill in blanks with the value above)
Use the reverse crosstable technique to unpivot your data. The code can be found here: http://community.qlik.com/message/78337#78337. This has the advantage of being agnostic to the values instead of hardcoding.