Trim spaces between data in qlikview

[apthansh]

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

[sunny_talwar]

Check this out:

The Generic Load

[apthansh]

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

[sunny_talwar]

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;

[julian_rodriguez]

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

[apthansh]

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‌

[antoniotiman]

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

[stevedark]

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

[DavidFoster1]

Another approach.

1. Use the file transformation wizard to fill in the missing ID values (you can fill in blanks with the value above)
2. 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.