topic Re: Transformation in QlikView

Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 06:17:33 GMT

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

Re: Transformation

whiteline — Wed, 11 Jun 2014 06:25:54 GMT

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

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 06:31:14 GMT

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()

));

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 06:37:35 GMT

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

Re: Transformation

jagan — Wed, 11 Jun 2014 06:41:31 GMT

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.

Re: Transformation

jagan — Wed, 11 Jun 2014 06:45:52 GMT

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.

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 06:53:01 GMT

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

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 06:54:34 GMT

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

Re: Transformation

jagan — Wed, 11 Jun 2014 06:57:35 GMT

Hi,

Try like this for month

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

Regards,

Jagan.

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 07:00:13 GMT

Try this

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

Re: Transformation

jagan — Wed, 11 Jun 2014 07:00:16 GMT

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.

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 07:02:08 GMT

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

Re: Transformation

jagan — Wed, 11 Jun 2014 07:12:47 GMT

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.

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 07:14:16 GMT

Hi Jagan,

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

like jan,feb,mar etc please suggest.

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 07:14:50 GMT

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()

));

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 07:22:02 GMT

Hi anand,

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

can you please suggest on this.

thanks

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 07:27:11 GMT

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.

Re: Transformation

deepakqlikview_123 — Wed, 11 Jun 2014 07:31:10 GMT

hi Anand,

i am still getting some null values in year field.

Thanks

Re: Transformation

its_anandrjs — Wed, 11 Jun 2014 07:36:54 GMT

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;

Re: Transformation

jagan — Wed, 11 Jun 2014 07:47:06 GMT

Hi Deepak,

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

Regards,

jagan.