Transformation

[deepakqlikview_123]

Hello Team,

please find attachment,

After making trasformations I am able to convert Rows to column in qlikview.

But can you please suggest how to hide null colums like F1,F2,F5,F26 in qlikview.

Thanks in advance.

Thanks

[whiteline]

Don't load them, remove from load statment manually.

[its_anandrjs]

In the transformation step you can comment them or do not load them

Eg:-

Load

//F1,

//F2,

//F3,

Fields1,

Fields2

From Location;

For Your script:-

Directory;

LOAD

     //F1,

     Title,

     //F3,

     [CDMS AUDIT LOG Size GB],

     //F5,

     [CDMS WSS CONTENT Size GB],

     //F7,

     [CDMSLive DATABASE Size GB],

     //F9,

     [LOG size CDMS AUDIT LOG Size GB],

     //F11,

     [LOG size CDMS WSS CONTENT Size GB],

     //F13,

     [LOG size CDMSLive Database Size GB],

     //F15,

     [Server (Available Space)],

     //F17,

     [Application Server-II 122 (465 GB)],

     //F19,

     [Application Server-I 123 (465 GB)],

     //F21,

     [Database Server 124  (C: 1.08 TB, 😧 464 GB, E: 464 GB)],

     //F23,

     [Database Mirror Server 125 (C: 1.08 TB, 😧 464 GB, E: 464 GB)]

     //F25,

     //F26,

     //F27

FROM

[Task details for 3 March 2014.xlsx]

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

Transpose()

));

[deepakqlikview_123]

Hi Anand,

Thanks for reply.

I am not able to retrive month from titel column.

Month(Title ) gives null calue can u please suggest.

Thanks

[jagan]

Hi,

Try like this

LOAD Title,

     [CDMS AUDIT LOG Size GB],

     [CDMS WSS CONTENT Size GB],

     [CDMSLive DATABASE Size GB],

     [LOG size CDMS AUDIT LOG Size GB],

     [LOG size CDMS WSS CONTENT Size GB],

     [LOG size CDMSLive Database Size GB],

     [Application Server-II 122 (465 GB)],

     [Application Server-I 123 (465 GB)],

     [Database Server 124  (C: 1.08 TB, 😧 464 GB, E: 464 GB)],

     [Database Mirror Server 125 (C: 1.08 TB, 😧 464 GB, E: 464 GB)]

FROM

[Task details for 3 March 2014.xlsx]

(ooxml, embedded labels, header is 1 lines, table is CDMS, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Transpose()

));

Regards,

Jagan.

[jagan]

Hi,

To retrieve Day, Month and Year, try like this

LOAD Title,

     [CDMS AUDIT LOG Size GB],

     [CDMS WSS CONTENT Size GB],

     [CDMSLive DATABASE Size GB],

     [LOG size CDMS AUDIT LOG Size GB],

     [LOG size CDMS WSS CONTENT Size GB],

     [LOG size CDMSLive Database Size GB],

     [Application Server-II 122 (465 GB)],

     [Application Server-I 123 (465 GB)],

     [Database Server 124  (C: 1.08 TB, 😧 464 GB, E: 464 GB)],

     [Database Mirror Server 125 (C: 1.08 TB, 😧 464 GB, E: 464 GB)],

     SubField(Replace(Title, 'Task Details for ', ''), ' ', 1) AS Day,

     SubField(Replace(Title, 'Task Details for ', ''), ' ', 2) AS Month,

     SubField(Replace(Title, 'Task Details for ', ''), ' ', 3) AS Year

FROM

[Task details for 3 March 2014.xlsx]

(ooxml, embedded labels, header is 1 lines, table is CDMS, filters(

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Transpose()

));

Regards,

jagan.

[its_anandrjs]

Add this line in the load script for Month field

You can try this for unique month values

Capitalize(SubField(Replace(Title, 'Task Details for ', ''), ' ', 2)) AS Month

[deepakqlikview_123]

Hi Jagan,

I am getting output but month are not in same format

eg for Feb I am getting 2 months feb and Februry.,In year I am getting december and in day I am getting some garbage value,please suggest.

Thanks

[jagan]

Hi,

Try like this for month

Capitalize(Left(SubField(Replace(Title, 'Task Details for ', ''), ' ', 2), 3)) AS Month,

Regards,

Jagan.

[its_anandrjs]

Try this

     Capitalize(SubField(Replace(Title, 'Task Details for ', ''), ' ', 2)) AS Month,