topic Re: Required Excel Formula in Qlikview in QlikView

Required Excel Formula in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Required Excel Formula in Qlikview

Thu, 21 Apr 2016 17:04:02 GMT

Any Solution on this?

Re: Required Excel Formula in Qlikview

settu_periasamy — Thu, 21 Apr 2016 17:12:19 GMT

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]

Re: Required Excel Formula in Qlikview

Thu, 21 Apr 2016 19:23:30 GMT

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

Re: Required Excel Formula in Qlikview

oscar_ortiz — Thu, 21 Apr 2016 20:26:04 GMT

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.

Re: Required Excel Formula in Qlikview

Fri, 22 Apr 2016 05:51:54 GMT

Thanks for the Reply.

I need Logic at Back end Script.

Re: Required Excel Formula in Qlikview

tamilarasu — Fri, 22 Apr 2016 06:32:36 GMT

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.

Re: Required Excel Formula in Qlikview

Fri, 22 Apr 2016 06:57:56 GMT

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

Re: Required Excel Formula in Qlikview

tamilarasu — Fri, 22 Apr 2016 08:02:08 GMT

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

Re: Required Excel Formula in Qlikview

sunny_talwar — Fri, 22 Apr 2016 08:27:54 GMT

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;

Re: Required Excel Formula in Qlikview

Fri, 22 Apr 2016 08:49:55 GMT

Thanks Sunny for the Reply.

Is this the Right Approach.

Left Join on the basis of RowNo.

I want to use Previous and Next Row in the same table.

Like this:

If(NAME=NEXTNAME,VALUE1,

IF(NAME=PREVIOUSNAME,VALUE2)) AS New_Code

Is this Possible.

Any idea on this

Re: Required Excel Formula in Qlikview

sunny_talwar — Fri, 22 Apr 2016 08:52:59 GMT

This is possible, but I think you need to provide a better sample with whatever you are expecting from us.