Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

Hi,

You need only records for 2012, then use

LOAD

     *

FROM DataSource

WHERE Year(Date)  = 2012;

HOpe this helps you.

Regards,

Jagan.

Not applicable
Author

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
CELAMBARASAN
Partner - Champion
Partner - Champion

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
Author

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