Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I want to generate Excel Formula in Qlikview.
I don't want to use DESC in ORDER BY.
I want to use below Formula in Qlikview: |
IF(A3=0,"-",IF(A3=A4,"-",B3)) In above Formula A3, A4 and B3 are Excel Cell Numbers. |
See the Attached Excel Sheet.
Regards
Eric
Any Solution on this?
May be try this expression. .
If (rowno ()=1 or NAME =Above (NAME) Or NAME=0,'-', [Tran Date])
Edit:
If you want script try
If (Rowno ()=1 or NAME=Previous (NAME) or NAME=0,'-',[Tran Date]) As [Desired Column]
I have Tried your Approach but I am not Getting Wrong Result.
settu_periasamy just about had it, instead of above use below.
If( NAME = 0, '-',
If( NAME = Below( NAME ), '-',
[Tran Date]
)
)
You'll want to use original sort order for your chart.
Thanks for the Reply.
I need Logic at Back end Script.
You can try,
Temp:
LOAD Rowno() as RowNo,
NAME,
[Tran Date],
[Desired Column]
FROM
[Excel Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Data:
Load *,
If(Len(Trim(NAME))=0,'-',If(NAME = Peek(NAME),'-',[Tran Date])) as [Desired Column (Script)]
Resident Temp Order by [Tran Date] Desc;
Drop table Temp;
Output:
Update: I guess, it's not possible to get the result without order the data by descending. You can use RecNo() or RowNo() function while loading the data from excel and sort it in front end using RowNo column. So, the output looks same as excel.
Thanks Tamil for the Reply.
I have no issue in using Order By Desc.
But my concern is that I have to use condition which includes both Asc and Desc in a single single Table
that's why i don't want to use Desc.
Is there any other Alternative.....??
Sorry. No idea. Maybe we can check with sunindia.
As far as I have seen, peeking ahead isn't possible in QlikView. not sure if these are the only three rows that you have, but for a small data base such as this, there might be other ways to do it, but if we are talking about a large dataset, then it can be a problem.
So I want to understand why Ordering by desc and asc is an issue? I think both can be done in two different resident loads. For this requirement you can do desc order and for the other one, do another resident load.
Since we were looking for an alternative, here is one I can think of (but like I said, I don't know how sustainable it is and how much it needs to change based on actual requirement)
Temp:
LOAD Rowno() as RowNo,
NAME,
[Tran Date],
[Desired Column]
FROM
[Excel Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join (Temp)
LOAD RowNo - 1 as RowNo,
NAME as NextName
Resident Temp;
FinalTable:
LOAD If(Len(Trim(NAME))=0, '-', If(NAME = NextName, '-', [Tran Date])) as [Desired Column (Script)],
NAME,
[Tran Date]
Resident Temp
Where Len(Trim(NAME)) > 0;
DROP Table Temp;