topic Re: QVD loading question in QlikView

QVD loading question

Thu, 20 Dec 2012 23:35:59 GMT

I want to load a QVD and discard certain records from a group when they match a certain criteria.

Lets say I have a QVD with the following table that has thousands of records loaded each month...

Multiple record per TxID

multiple Record types

recID TxID Record Type Date

1 10 A 201209

2 10 C 201211

3 11 A 201211

4 11 C 201209

5 11 B 201210

6 11 A 201109

7 12 A 201209

As you can see, records 3 thru 6 belong to the same TxID but record 6 has a date from a different time period.

How do I load this QVD and discard all the records of Record Type "A" in a Tx group that have a date before the date in the record of Record Type "B" of the same transaction group.

I can figure this out in SQL but I am new to Qlikview and haven't wuite got my head around this stuff yet!

Thanks

Re: QVD loading question

jagan — Fri, 21 Dec 2012 06:49:39 GMT

Hi,

You need only records for 2012, then use

LOAD

FROM DataSource

WHERE Year(Date) = 2012;

HOpe this helps you.

Regards,

Jagan.

Re: QVD loading question

Fri, 21 Dec 2012 15:04:54 GMT

Thanks but not quite that simple.

What I need is to not load a record of Recort Type "A" when the date is older than the date in Record Type "B" with the same Tx group ID.

So in the table below, records with recID 6 and 10 wouldn't be loaded.

recID	TxID	Record Type	Date
1	10	A	201209
2	10	C	201211
3	11	A	201211
4	11	C	201209
5	11	B	201210
6	11	A	201109
7	12	A	201209
8	12	C	201210
9	12	B	201208
10	12	A	201207
11	12	C	201209
12	13	A	201209

Re: QVD loading question

CELAMBARASAN — Fri, 21 Dec 2012 15:22:06 GMT

Try this

Load * Where flag=0;

LOAD

recID, TxID, [Record Type], Date, if(Date#(Previous(Date),'YYYYMM')>Date#(Date,'YYYYMM') AND [Record Type] = 'A', 1, 0) AS flag

FROM QVDSource;

Hope it helps

Re: QVD loading question

Fri, 21 Dec 2012 15:56:43 GMT

getting there!

But there is no implied order. My example might imply order but that was careless on my part.

It needs to do a lookup, if possible to get the date of the Type "B" record in a Tx group and compare the dates in the Type "A" records for that Tx group. Is it possible to compare to a temp table?

Also, why is the Record Type in you example in square brackets?

Thanks