Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

QVD loading question

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

4 Replies
MVP
MVP

Re: QVD loading question

Hi,

You need only records for 2012, then use

LOAD

     *

FROM DataSource

WHERE Year(Date)  = 2012;

HOpe this helps you.

Regards,

Jagan.

Not applicable

Re: QVD loading question

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   10A   201209
2   10201211
3   11 A   201211
4   11201209
5   11B   201210
6   11A   201109
7   12A   201209
8   12C   201210
9   12 B   201208
10 12 A   201207
11   12201209
12   13201209

Re: QVD loading question

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

Not applicable

Re: QVD loading question

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

Community Browser