topic Load Statement Question in QlikView

Load Statement Question

Wed, 17 Jun 2009 22:43:56 GMT

Hello,

I'd like to filter the field Desc_text during load. Data looks like this:

UserNr. CaseNr. Desc_Text Date user_Status
=============================================================
17 101 Text1 08.07.08 New
17 201 Text1 08.07.08 Known
17 301 Text2 22.08.08 Known
17 301 Text1 17.08.08 Known
17 301 Text3 22.08.08 Known

Outcome should look like:

UserNr. CaseNr. Desc_Text Date user_Status
=============================================================
17 101 Text1 08.07.08 New
17 301 Text2 22.08.08 Known
17 301 Text3 22.08.08 Known

Thank you.

Regards

Load Statement Question

hectorgarcia — Thu, 18 Jun 2009 00:41:52 GMT

use max function in the date field and group by the rest fields

Load Statement Question

johnw — Thu, 18 Jun 2009 04:10:23 GMT

If you mean this...

MyData:
LOAD
 UsrNr.
,CaseNr.
,Desc_Text
,user_Status
,max(Date) as Date
FROM <source>
GROUP BY
 UsrNr.
,CaseNr.
,Desc_Text
,user_Status
;

... it won't work because the case numbers and user_Status change, so you'd just get the input table. You also want the min function instead of max function. You're on the right track, though. I'd handle it like this:

MyData:
LOAD *
FROM <source>
;
INNER JOIN
LOAD
 Desc_Text
,min(Date) as Date
RESIDENT MyData
GROUP BY Desc_Text
;

Technically, that won't work either - you'll get two rows for Text1 08.07.08. How to resolve that depends on the specifics of your problem. Are you choosing New because New takes priority over Known? Are you choosing New because the CaseNr is lower? Is the Date in your example actually a timestamp, and New has the lowest timestamp?

Load Statement Question

Karl_Humma — Thu, 18 Jun 2009 06:54:05 GMT

try something like this



test:
load * inline [
 UserNr.,CaseNr.,Desc_Text,Date,user_Status
 17,101,Text1,08.07.08,New
 17,201,Text1,08.07.0,Known
 17,301,Text2,22.08.08,Known
 17,301,Text1,17.08.08,Known
 17,301,Text3,22.08.08,Known
 ];

test2:
noconcatenate Load *
resident test
 Where Desc_Text <> previous(Desc_Text) order by Desc_Text asc;

drop table test;

it worked for me.

/Karl

Load Statement Question

johnw — Thu, 18 Jun 2009 07:42:51 GMT

In this case, you took the first record that was presented to you when you sorted by Desc_Text. I don't know about QlikView, but in DB2, for instance, the order of the records WITHIN a given Desc_Text is not guaranteed unless you sort by other fields within it. I wouldn't make the assumption that it is guaranteed in QlikView, either, unless you know for certain that it is. Also, I'm not certain that we can guarantee that the source data is in the desired order for a given Desc_Text. It might not be. Regardless, this takes me back to my question on how we decide which record to use. You used a generic idea of "the first one", but I believe it should be more explicit - that we need to know what we MEAN by "the first one".

Load Statement Question

Thu, 18 Jun 2009 21:40:00 GMT

1) Make sure you have an ORDER BY Desc_Text clause on your data to ensure that you get the "first" item you want in each group.

2) Create a field using the "previous" function to determine whether this is the "first" line for a given Desc_Text:

if(Desc_Text=previous(Desc_Text),0,1) AS MY_FLAG

3) Include a WHERE MY_FLAG = 0 clause

Load Statement Question

Sun, 21 Jun 2009 19:08:39 GMT

Hi,

thanks for your answers, unfortunately I'm still having problems:

I'm working on a helpdesk bar chart.

I would like to know how many users (count on User) contacted in a selected period the helpdesk because of which problem (ErrorText). The Users should be grouped by Status New, Existing (this I would handle with an if clause in diagram expression if(User Status = 'New',....)).

My problem is that I don't get the right data to my bar chart.

This is data sample data for user 68:

LOAD * INLINE [
User, TicketDate, TicketNr, ErrorText, User Status
68, 08.07.2008, XT1001, Text1, Existing
68, 08.07.2008, XT1001, Text2, Existing
68, 08.07.2008, XT1002, Text1, Existing
68, 08.07.2008, XT1002, Text2, Existing
68, 08.07.2008, XT2001, Text1, New
68, 08.07.2008, XT2001, Text2, New
68, 08.07.2008, XT2002, Text2, Existing
68, 08.07.2008, XT2003, Text1, Existing
68, 08.07.2008, XT2003, Text2, Existing
68, 12.07.2008, XT3001, Text3, Existing
68, 24.08.2008, XT3002, Text3, Existing
68, 09.09.2008, XT3003, Text4, Existing
68, 09.09.2008, XT3003, Text3, Existing
68, 11.10.2008, XT4001, Text5, Existing
68, 11.10.2008, XT4001, Text3, Existing
];

I'm trying to create 2 "versions" of the chart. One with Distinct ErrorText per Day, one with every ErrorText per Day.

Thanks.

Load Statement Question

Mon, 22 Jun 2009 14:52:51 GMT

There was a mistake in LoadScript, Lowest TicketNr should be Status New

LOAD * INLINE [
User, TicketDate, TicketNr, ErrorText, User Status
68, 08.07.2008, XT1001, Text1, New
68, 08.07.2008, XT1001, Text2, New
68, 08.07.2008, XT1002, Text1, Existing
68, 08.07.2008, XT1002, Text2, Existing

68, 08.07.2008, XT2001, Text1, Existing
68, 08.07.2008, XT2001, Text2, Existing
68, 08.07.2008, XT2002, Text2, Existing
68, 08.07.2008, XT2003, Text1, Existing
68, 08.07.2008, XT2003, Text2, Existing
68, 12.07.2008, XT3001, Text3, Existing
68, 24.08.2008, XT3002, Text3, Existing
68, 09.09.2008, XT3003, Text4, Existing
68, 09.09.2008, XT3003, Text3, Existing
68, 11.10.2008, XT4001, Text5, Existing
68, 11.10.2008, XT4001, Text3, Existing
];

Load Statement Question

Mon, 22 Jun 2009 21:47:56 GMT

Hi *,

I tried

LOAD
User,
ErrorText,
User_Status,
date(min(TicketDate),'DD.MM.YYYY') as Date
RESIDENT Test
GROUP BY TicketDate, ErrorText, User, User_Status
;

Result is looking good.

But the Text1/2 is currently counted twice because of User_Status. Is there a chance to get rid of
exisiting ones if there are "the same errors" with status "new" on a certain day?

Load Statement Question

johnw — Tue, 23 Jun 2009 00:28:49 GMT

Is this what you want?

Load Statement Question

Tue, 23 Jun 2009 01:55:46 GMT

Hi John,

great. Exactly what I was looking for. Thank you very much.