Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need help in scripting. to get the data.
I have a table which is resident in the qlikview.
in my table i have many columns and i need to ably group by caluse in some columns. My problem is how do i include the other column in the data. Please help me out to solve this issue.
I am attaching the sample data. I need data for the given order_number, for a given measure which is having the minimum priority.
ORDER_NUMBER | MEASURE | LATE_CODE | PRIORITY | CREATION_DATE | INCLUSION_INDICATOR | INVENTORY_ORG | LATE_CODE_DESCRIPTION | LINE_SCHED | OP_SUB_CATEGORY | OPERATIONAL_DESC |
100059998 | PROMISED | 18 | 8 | 18-01-2011 15:16 | N | 050 | Due date was a holiday/weekend | 3-1 | Holiday | Non-Operational |
100059998 | REQUESTED | 12 | 8 | 12-01-2011 12:17 | N | 050 | Other (Requires Comment) | 1-1 | Other | Operational |
100059998 | REQUESTED | 12 | 8 | 25-03-2011 15:13 | N | 050 | Other (Requires Comment) | 8-1 | Other | Operational |
100059998 | REQUESTED | 18 | 11 | 18-01-2011 15:16 | N | 050 | Due date was a holiday/weekend | 3-1 | Holiday | Non-Operational |
100059998 | SCHEDULED | 18 | 3 | 18-01-2011 15:16 | N | 050 | Due date was a holiday/weekend | 3-1 | Holiday | Non-Operational |
Final result should be like.
ORDER_NUMBER | MEASURE | LATE_CODE | PRIORITY | CREATION_DATE | INCLUSION_INDICATOR | INVENTORY_ORG | LATE_CODE_DESCRIPTION | LINE_SCHED | OP_SUB_CATEGORY | OPERATIONAL_DESC |
100059998 | PROMISED | 18 | 8 | 18-01-2011 15:16 | N | 050 | Due date was a holiday/weekend | 3-1 | Holiday | Non-Operational |
100059998 | SCHEDULED | 18 | 3 | 18-01-2011 15:16 | N | 050 | Due date was a holiday/weekend | 3-1 | Holiday | Non-Operational |
100059998 | REQUESTED | 12 | 8 | 25-03-2011 15:13 | N | 050 | Other (Requires Comment) | 8-1 | Other | Operational |
Please help me out to solve this problem.
Thanks
Sushil
Sushil,
The code would look something like this, assuming your first table is called myTable:
myTableFinal:
LOAD
ORDER_NUMBER,
min(PRIORITY) as PRIORITY
RESIDENT myTable
GROUP BY ORDER_NUMBER
;
LEFT JOIN (myTableFinal) LOAD
ORDER_NUMBER,
PRIORITY,
MEASURE,
LATE_CODE,
PRIORITY,
CREATION_DATE,
INCLUSION_INDICATOR,
INVENTORY_ORG,
LATE_CODE_DESCRIPTION,
LINE_SCHED,
OP_SUB_CATEGORY,
OPERATIONAL_DESC
RESIDENT myTable;
DROP TABLE myTable;
Regards,
Vlad
Hi,
Try this
INNER JOIN(OriginalResidentTable)
LOAD
ORDER_NUMBER,
MEASURE,
Min(PRIORITY) AS MinPriority
Resident OriginalResidentTable
Group by ORDER_NUMBER, MEASURE;
Hope this helps you.
Regards,
Jagan.