Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
You need only records for 2012, then use
LOAD
*
FROM DataSource
WHERE Year(Date) = 2012;
HOpe this helps you.
Regards,
Jagan.
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 |
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
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