Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Check this
Title,
Year,
Day,
Dual(Month, Match(Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun','Jul','Aug','Sep','Oct','Nov','Dec')) AS Month;
In Sort tab Select Numeric Ascending for Month Field.
Can you close this by giving Correct and Helpful answers to the posts.
jagan.
Hi Jagan,
This is working,but I am not getting month in ascending order
like jan,feb,mar etc please suggest.
Try Only this
Tmp:
[Server (Available Space)],
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
[Task details for 3 March 2014.xlsx]
(ooxml, embedded labels, table is CDMS, filters(
Hi anand,
I am getting some null values in month and year selection.
can you please suggest on this.
thanks
Update little bit change in the script please find the load script
Hi Deepak,
Check this load statement, Main transformation in the Bold
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
Final:
NoConcatenate
LOAD * Resident Tmp Where Len(Year) > 0;
DROP Table Tmp;
Let me know if having the problem.
hi Anand,
i am still getting some null values in year field.
After the transformation table in resident table only put this line Where Len(Year) > 0
Tmp;
Load
..
Did you checked script in my last post? It is working as expected.
