topic Maxstring and Distinct in QlikView

Maxstring and Distinct

Wed, 28 Oct 2015 15:52:58 GMT

I have a table with 4 columns, I need to know how to do something in the load script like the following.

MaxString(SalePrimaryKey), Distinct(Employee), Sales, Count. Does anyone have any ideas?

Re: Maxstring and Distinct

cesaraccardi — Wed, 28 Oct 2015 16:19:11 GMT

Try this:

LOAD

Employee,

Maxstring(SalePrimaryKey), as SalePrimaryKey,

FirstSortedValue(Sales) as Sales,

FirstSortedValue(Count) as Count

GROUP BY

Employee;

Re: Maxstring and Distinct

sunny_talwar — Wed, 28 Oct 2015 16:36:36 GMT

Do you mean FirstValue instead of FirstSortedValue? FirstSortedValue takes two inputs:

LOAD

Employee,

Maxstring(SalePrimaryKey), as SalePrimaryKey,

FirstValue(Sales) as Sales,

FirstValue(Count) as Count

GROUP BY

Employee

But somehow the above doesn't seem right as we are not sorting the table. Zeth would you be able to share some sample data with expected output?

Re: Maxstring and Distinct

cesaraccardi — Wed, 28 Oct 2015 16:56:06 GMT

Yes, thats right thanks. I meant FirstValue(), alternatively those fields could be listed on the group by clause:

LOAD

Employee,

Maxstring(SalePrimaryKey), as SalePrimaryKey,

Sales,

Count

GROUP BY

Employee, Sales, Count;

Re: Maxstring and Distinct

Thu, 29 Oct 2015 18:58:17 GMT

This is the initial data, you can see employeeID 546 is duplicated. I want to return the Employee ID with the greatest SalePrimary Key.

SalePrimaryKey	EmployeeID	Sales	Count
1	546	500	7
2	128	1000	9
3	546	1250	5
4	896	50	1
5	452	1583	84
6	546	1540	4
7	236	1531	2

So it would return the following.

SalePrimaryKey	EmployeeID	Sales	Count
2	128	1000	9
4	896	50	1
5	452	1583	84
6	546	1540	4
7	236	1531	2

Re: Maxstring and Distinct

sunny_talwar — Thu, 29 Oct 2015 19:15:46 GMT

May be this:

Table:

LOAD * Inline [

SalePrimaryKey, EmployeeID, Sales, Count

1, 546, 500, 7

2, 128, 1000, 9

3, 546, 1250, 5

4, 896, 50, 1

5, 452, 1583, 84

6, 546, 1540, 4

7, 236, 1531, 2

];

FinalTable:

NoConcatenate

LOAD EmployeeID,

FirstSortedValue(SalePrimaryKey, -SalePrimaryKey) as SalePrimaryKey,

FirstSortedValue(Sales, -SalePrimaryKey) as Sales,

FirstSortedValue(Count, -SalePrimaryKey) as Count

Resident Table

Group By EmployeeID;

DROP Table Table;

Re: Maxstring and Distinct

Thu, 29 Oct 2015 19:47:04 GMT

This keeps returning a syntax error, missing/misplaced FROM:

Re: Maxstring and Distinct

sunny_talwar — Fri, 30 Oct 2015 01:06:47 GMT

I won't be able to see the same error, because I am using an inline load. Would you be able to share your script and the exact error that you are seeing???

Best,

Sunny

Re: Maxstring and Distinct

jagan — Fri, 30 Oct 2015 02:20:30 GMT

HI,

Try Inner Join using the simple script below

Table:

LOAD * Inline [

SalePrimaryKey, EmployeeID, Sales, Count

1, 546, 500, 7

2, 128, 1000, 9

3, 546, 1250, 5

4, 896, 50, 1

5, 452, 1583, 84

6, 546, 1540, 4

7, 236, 1531, 2

];

INNER JOIN(Table)

LOAD EmployeeID,

Max(SalePrimaryKey)

Resident Table

Group By EmployeeID;

Hope this helps you.

Regards,

Jagan.