How to do this in Qlikview

[#1]

Hi Experts

I want this in Qlikview Script:

Main File Data:

Desired Output:

Sample File is Attached for Reference.

Regards

Eric

[#2]

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

[#3]

Thanks Varsha

I want this from Back end at Script Level.

[Digvijay_Singh]

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

Source:

LOAD Case,

     Flag,

     Category,

     ID

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;

[Digvijay_Singh]

Sample attached -

[#6]

The script is as follows:

Temp:

LOAD Case,

     Flag,

     Category,

     ID

FROM

Sample_Logic.xlsx

(ooxml, embedded labels, table is Sheet1);

Output:

NoConcatenate

Load Case,

     Flag,

     Category,

     ID

Resident Temp

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

Order By Case desc, Category desc, Flag asc;

Drop Table Temp;

[varshavig12]

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;