topic Re: How to do this in Qlikview in QlikView

How to do this in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts

I want this in Qlikview Script:

Main File Data:

Desired Output:

Sample File is Attached for Reference.

Regards

Eric

Re: How to do this in Qlikview

Mon, 20 Jun 2016 03:19:24 GMT

Here is a sample for how you could limit the load in the script: (I am basing this on what looks like your primary limiters are Unique Case & Unique Category)

Re: How to do this in Qlikview

sunny_talwar — Mon, 20 Jun 2016 03:20:50 GMT

What is the logic that is driving the output you are looking for?

Re: How to do this in Qlikview

settu_periasamy — Mon, 20 Jun 2016 03:31:20 GMT

Seems to be you want the first value in the 'ID'.

May be try like..

LOAD Case,
Flag,
Category,
FirstValue(ID) as ID
FROM
Sample_Logic.xlsx
(ooxml, embedded labels, table is Sheet1) Group by Case,Flag,Category;

Re: How to do this in Qlikview

varshavig12 — Mon, 20 Jun 2016 03:41:25 GMT

Script:

Directory;

LOAD Case,

Flag,

Category,

firstvalue(ID) as ID

FROM

a.xlsx

(ooxml, embedded labels, table is Sheet1)

Group by Category,Case,Flag;

Dimesion:

Expresssion:

if(Category<>above(Category),ID)

Output:

Re: How to do this in Qlikview

phaneendra_kunc — Mon, 20 Jun 2016 03:45:59 GMT

Seems he is looking load only first occurrence.

You can do with FirstValue() or Previous() with if statement.

Re: How to do this in Qlikview

varshavig12 — Mon, 20 Jun 2016 03:49:07 GMT

I guess, one can do this with aggr function.

Re: How to do this in Qlikview

sunny_talwar — Mon, 20 Jun 2016 03:51:47 GMT

But then why did GHI 3 Wrong not included in the result?

Re: How to do this in Qlikview

phaneendra_kunc — Mon, 20 Jun 2016 04:25:51 GMT

He is picking up/ going by Category not on Flag.

Re: How to do this in Qlikview

jonathandienst — Mon, 20 Jun 2016 05:06:10 GMT

Perhaps the original poster would like to comment on the speculation here.

Re: How to do this in Qlikview

Mon, 20 Jun 2016 05:35:27 GMT

Thanks everyone for your answers.

This is the Logic requirement:

1) This is a case (FieldName) wise report.

2) Need to check the Flag, Category and ID coming in case.

3) Example, In case ABC there is only one Flag '1' and one Category i.e. 'Right'

So return it's first ID i.e. ABC123

4) For Case DEF, there are 2 Flags '1' and '3' and Flag '1' has only 'Right' value

so, for DEF Flag '1' and 'Right' category the ID would be 'ABC20'

and for DEF Flag '3' and Category 'Wrong' the ID would be 'XYZ777'

5) For case GHI, there are also 2 Flags '1' and '3' and only one 'Category' i.e. wrong

so here pick the minimum Flag i.e. '1' and return the first ID i.e. 'XYZ1200'

6) For case JKL, there are 2 Flags i.e. '1' and '3' and against '1' Flag

there is Category 'Right' so here the ID would be 'XYZ883' and for

JKL Wrong Category there are 2 Flags '1' and '3' so pick the mimimum Flag

i.e. and 1 and display its mimimum value over here i.e. XYZ800.

Hope, I am clear with my requirement.

Regards

Eric

Re: How to do this in Qlikview

Mon, 20 Jun 2016 05:53:35 GMT

Thanks Varsha

I want this from Back end at Script Level.

Re: How to do this in Qlikview

Digvijay_Singh — Mon, 20 Jun 2016 13:40:02 GMT

You can create SeqNo field in the script and use firstsortedvalue to derive the required information -

Source:

LOAD Case,

Flag,

Category,

FROM

Sample_Logic.xlsx

(ooxml, embedded labels, table is Sheet1);

Final:

LOAD Case,

Flag,

Category,

ID,

Rowno() as SeqNo

Resident Source

Order By Case,Category,Flag;

Drop Table Source;

Re: How to do this in Qlikview

Digvijay_Singh — Mon, 20 Jun 2016 13:41:16 GMT

Sample attached -

Re: How to do this in Qlikview

Wed, 22 Jun 2016 01:19:46 GMT

The script is as follows:

Temp:

LOAD Case,

Flag,

Category,

FROM

Sample_Logic.xlsx

(ooxml, embedded labels, table is Sheet1);

Output:

NoConcatenate

Load Case,

Flag,

Category,

Resident Temp

Where NOT(Peek(Case)=Case and Peek(Category)=Category)

Order By Case desc, Category desc, Flag asc;

Drop Table Temp;

Re: How to do this in Qlikview

varshavig12 — Wed, 22 Jun 2016 05:24:57 GMT

abc:

LOAD Case,

Flag,

Category,

firstvalue(ID) as ID

FROM

a.xlsx

(ooxml, embedded labels, table is Sheet1)

where Peek(Category)<>Category and Peek(Case)<>Case

Group by Category,Case,Flag;

right join

lmn:

load Case, min(Flag) as Flag, Category

resident abc

group by Category,Case;