Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Required Excel Formula in Qlikview

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.

Excel Formula.png

See the Attached Excel Sheet.

Regards

Eric

11 Replies
Not applicable
Author

Any Solution on this?

settu_periasamy
Master III
Master III

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]

Not applicable
Author

I have Tried your Approach but I am not Getting Wrong Result.

oscar_ortiz
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

Thanks for the Reply.

I need Logic at Back end Script.

tamilarasu
Champion
Champion

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:

Capture.PNG

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.

Not applicable
Author

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.....??

tamilarasu
Champion
Champion

Sorry. No idea. Maybe we can check with sunindia.

sunny_talwar

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;


Capture.PNG