topic Grouping data at diff. level in QlikView

Grouping data at diff. level

Thu, 02 Jul 2009 00:19:22 GMT

Hello every one,

I have data, which had batch id and cycle id, per batch there will be one or more cycleid.

I have diff, date which I want to find out, basically analytical function.

, you can see I have diff group in both load, How can i do in QlikView, Is there any analytical function which I can use. I oracle we have first and last vale function and you can use partition by clause to divide in to diff group, Can we do this in one load in Qlikview? I am not bale to figure out this, please help me.

Something like this


QueueHistory:
INNER KEEP
LOAD autonumber(QUEUEID+ENTITYID+QUEUEINDATE) as KeyQueueHistoryId,
QUEUEID as KeyQueueId,
ENTITYID as KeyBatchId,
floor(QUEUEINDATE) as [Queue Start Date],
hour(QUEUEINDATE) as [Queue Start Hour],
minute(QUEUEINDATE) as [Queue Start Minute],
second(QUEUEINDATE) as [Queue Start Second],
floor(QUEUEOUTDATE) as [Queue End Date],
hour(QUEUEOUTDATE) as [Queue End Hour],
minute(QUEUEOUTDATE) as [Queue End Minute],
second(QUEUEOUTDATE) as [Queue End Second],
floor(TARGETQUEUEOUTDATE) as [Queue Due Date],
QUEUEUSERID as QueueUserId,
CYCLEID as CycleId,
IOFLAG as IOFlag,
autonumber(ENTITYID+CYCLEID) as KeyBatchCycle;
SQL
SELECT * from Table_data;

QueueHistorySummary:
LOAD autonumber(KeyBatchId + CycleId ) as KeyBatchCycle,
// KeyBatchId &'-'& CycleId as KeyBatchCycle,
// CycleId as CycleId,
 MIN([Queue Start Date]) as [Cycle Start Date],
 MAX([Queue End Date]) as [Cycle End Date],
 MIN(if(match(num(KeyQueueId),1002,1019)>0,[Queue Start Date])) as [QA Start
 Date],
 MAX(if(match(num(KeyQueueId),1002,1019)>0,[Queue End Date])) as [QA End
 Date],
 MAXSTRING(IF(KeyQueueId=1003,'Y','N')) as QaFailedResult
RESIDENT QueueHistory
GROUP BY KeyBatchId,CycleId ;

Left join (QueueHistorySummary)
LOAD KeyBatchId ,
 max(if(num(KeyQueueId)=1004,[Queue End Date])) as [Batch Comp Date]
resident QueueHistory
group by KeyBatchId;

Grouping data at diff. level

Oleg_Troyansky — Thu, 02 Jul 2009 01:04:55 GMT

You are almost there. Just remember two rules:

When you use GROUP BY to summarize, all your "key" fields need to be listed in the GROUP BY clause, even if it's a calculated formula.
When you join tables, you have to have your join keys present both in the table and in the join load.

The following *should* work. I marked the changes with Bold and Underline formatting. If you don't want to have KeyBatchID in the Summary Table, you can drop it later, but you need to have it at first, for the join to work...

good luck!

Oleg

QueueHistory:

INNER KEEP

LOAD autonumber(QUEUEID+ENTITYID+QUEUEINDATE) as KeyQueueHistoryId,

QUEUEID as KeyQueueId,

ENTITYID as KeyBatchId,

floor(QUEUEINDATE) as [Queue Start Date],

hour(QUEUEINDATE) as [Queue Start Hour],

minute(QUEUEINDATE) as [Queue Start Minute],

second(QUEUEINDATE) as [Queue Start Second],

floor(QUEUEOUTDATE) as [Queue End Date],

hour(QUEUEOUTDATE) as [Queue End Hour],

minute(QUEUEOUTDATE) as [Queue End Minute],

second(QUEUEOUTDATE) as [Queue End Second],

floor(TARGETQUEUEOUTDATE) as [Queue Due Date],

QUEUEUSERID as QueueUserId,

CYCLEID as CycleId,

IOFLAG as IOFlag,

autonumber(ENTITYID+CYCLEID) as KeyBatchCycle;

SQL

SELECT * from Table_data;

QueueHistorySummary:

LOAD autonumber(KeyBatchId + CycleId ) as KeyBatchCycle,

KeyBatchId ,

// KeyBatchId &'-'& CycleId as KeyBatchCycle,

// CycleId as CycleId,

MIN([Queue Start Date]) as [Cycle Start Date],

MAX([Queue End Date]) as [Cycle End Date],

MIN(if(match(num(KeyQueueId),1002,1019)>0,[Queue Start Date])) as [QA Start

Date],

MAX(if(match(num(KeyQueueId),1002,1019)>0,[Queue End Date])) as [QA End

Date],

MAXSTRING(IF(KeyQueueId=1003,'Y','N')) as QaFailedResult

RESIDENT QueueHistory

GROUP BY KeyBatchId,

autonumber(KeyBatchId + CycleId )

;

Left join (QueueHistorySummary)

LOAD KeyBatchId ,

max(if(num(KeyQueueId)=1004,[Queue End Date])) as [Batch Comp Date]

resident QueueHistory

group by KeyBatchId;

Grouping data at diff. level

Mon, 06 Jul 2009 22:04:59 GMT

Thank you so much , I will try it.