Load Statement Question

[#1]

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

[hectorgarcia]

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

[johnw]

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?

[Karl_Humma]

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

[johnw]

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".

[#6]

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

[#7]

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.

[#8]

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
];

[#9]

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?

[johnw]

Is this what you want?