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

[jagan]

Hi,

Try this script, this will fix all your issues.

LOAD

*,

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

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

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

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

     Replace(Replace(Title, 'Task Details for ', ''), '  ', ' ') AS TempTitle   

FROM

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

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

Transpose()

));

Regards,

Jagan.

[deepakqlikview_123]

Thanks Jagan,

It looks like perfect.

Can u please suggest how to format for day and year and how to get value in ascending order.

Eg for month first jan,feb,--dec.

Thanks

[jagan]

Hi,

Check 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)],

     Year,

     Day,

Dual(Month, Match(Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun','Jul','Aug','Sep','Oct','Nov','Dec')) AS Month;

LOAD

*,

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

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

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

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

     Replace(Replace(Title, 'Task Details for ', ''), '  ', ' ') AS TempTitle   

FROM

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

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

Transpose()

));

In Sort tab Select Numeric Ascending for Month Field.

Can you close this by giving Correct and Helpful answers to the posts.

Regards,

jagan.

[deepakqlikview_123]

Hi Jagan,

This is working,but I am not getting month in ascending order

like jan,feb,mar etc  please suggest.

[its_anandrjs]

Try Only this

Tmp:

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],

        [Server (Available Space)],

        [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,

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

KeepChar(SubField(Replace(Title, 'Task Details for ', ''), ' ', 3),0123456789) AS Year //Because Year has december word also to remove use this

FROM

[Task details for 3 March 2014.xlsx]

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

Transpose()

));

[deepakqlikview_123]

Hi anand,

I am getting some null values in month and year selection.

can you please suggest on this.

thanks

[its_anandrjs · Accepted solution]

Update little bit change in the script please find the load script

Hi Deepak,

Check this load statement, Main transformation in the Bold

Tmp:

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],

     [Server (Available Space)],

     [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)],

     NUM(Day(Date#(SubField(Replace(Title, 'Task Details for ', ''), ' ', 1),'DD'))) AS Day,

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

     NUM(Month(Date#(Capitalize(Left((SubField(Replace(Title, 'Task Details for ', ''), ' ', 2)),3)),'MMM'))) AS NumMonth,

     KeepChar(Date#(SubField(Replace(Title, 'Task Details for ', ''), ' ', 3),'YYYY'),0123456789) AS Year,

     MakeDate(KeepChar(Date#(SubField(Replace(Title, 'Task Details for ', ''), ' ', 3),'YYYY'),0123456789),

           Month(Date#(Capitalize(Left((SubField(Replace(Title, 'Task Details for ', ''), ' ', 2)),3)),'MMM')),

           NUM(Day(Date#(SubField(Replace(Title, 'Task Details for ', ''), ' ', 1),'DD')))

           ) as DateField

FROM

[Task details for 3 March 2014.xlsx]

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

Transpose()

));

Final:

NoConcatenate

LOAD * Resident Tmp  Where Len(Year) > 0;

DROP Table Tmp;

Let me know if having the problem.

[deepakqlikview_123]

hi Anand,

i am still getting some null values in year field.

Thanks

[its_anandrjs]

After the transformation table in resident table only put this line  Where Len(Year) > 0

Tmp;

Load

..

..

Final:

NoConcatenate

LOAD * Resident Tmp  Where Len(Year) > 0;

DROP Table Tmp;

[jagan]

Hi Deepak,

Did you checked script in my last post?  It is working as expected.

Regards,

jagan.