topic Re: Need help in scripting... in QlikView

Need help in scripting...

sushil353 — Mon, 19 Nov 2012 15:08:16 GMT

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

Re: Need help in scripting...

vgutkovsky — Mon, 19 Nov 2012 15:30:04 GMT

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

Re: Need help in scripting...

jagan — Mon, 19 Nov 2012 15:33:06 GMT

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.