
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
QlikSense check trade exist or not in next day csv extract
Hi,
We have one requirement like
Data Format: .csv file (in multiple folders)
Frequency: Daily
Load Folder structure: will have all legacy data from past 2 years
Loaded data from all multiple folders and created one final QVD using below condition as reference:
FOR Each folder in 'ABC','BCD','DEF','EFG','FGH','GHI','HIK',
FOR Each file in FileList ('lib://Sample\$(folder)\*20201006*.csv')
Fact:
Load *
FROM [$(file)]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
NEXT file
NEXT folder
Output: all the folders data was now merged into one single QVD
now issue is like data was in below mentioned format:
Day 1 | |
ID | Status (calculated column) |
111 | Unmatched |
222 | Unmatched |
333 | Unmatched |
Day 2 | |
ID | Status (Calculated Column) |
222 | Unmatched |
333 | Unmatched |
444 | Unmatched |
Day 3 | |
ID | Status (Calculated Column) |
333 | Unmatched |
444 | Unmatched |
555 | Unmatched |
now issue is like the "ID" present in "Day 1" extract is "UnMatched", and just in case if its not available in "Day 2" then "Status" is "Matched"(calculated)
Like wise:
1. We need to load complete .CSV files and check with other day file whether that ID is present or not in next day file
2. IF yes then Status : Unmatched
IF no then Status : Matched and stamp Day 2 date in another calculated column date
Hope I have managed to explain the issue well.
Thanks in advance for your help

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
need to merge data from all the folders and then need to identify max date (the date when it was missing)
and concurrently I need to maintain history as well into Qlik in QVD format.
As per date/month/year selection need to display the no of matched dates

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Gysbert_Wassenaar can please let me know any solution for this
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is not that easy, but let's see if @marcus_sommer can help on this? I see this is required permutation and combination.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Attached sample data for reference, unable to upload into CSV format and so attached .xlsx
Can please save as to 3 multiple CSV files.
Here is the sample expected result:
**Status and Date are calculated columns


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If I understand it right you want to create additionally records which then contain the state of "matched"? Does it always relate to the previous day or to all available days or anyhow different to it? How many records respectively unique field-values of ID and DAY exists?
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @marcus_sommer ,
Thank you for your reply.
We have data in CSV format from past 4 years, from day 1 we need to search the "ID" is present in next consecutive day report(it can be any day report say like can be until 20th day or so) or not.
**At any point of time, we should be able to do full reload, just in case QVD got corrupted or so and so irrespective of any day the ID checking in new files is common
1. Daily base report is always unmatched report , so by default Status(calculated column can be stamped as "Unmatched")
2. ID in day 1 can be present in any day report say like it can repeat for next 10 or N days
3. the day it got missed in the daily report is the one we need to pick up and stamp the date in a new calculated column
4. In total we required 2 new additional columns:
a. Status : Matched/Unmatched
b. Date In: The day any particular ID is missing from the next consecutive report
5. Coming to Count, in front end if we are selecting
a. Day 1 in master calendar it should show total un matched records as : 4 (as per sample file provided)
b. Likewise for day 2 and day 3
c. concurrently it should show matched or not
In a summary:
Highlighted in bold: new calculated columns
font color (Red) : Total Records
Font color(Green): The one matched(missing from that day particular report)
COB Feb 06th – 111 Matched Trades from 131 - Date In and Date Complete to be marked as 07/02/2021
COB Feb7th 30th – 31 Matched Trades from 74 - Date In and Date Complete to be marked as 08/02/2021
COB Feb 9th 1st – 55 Matched Trades from 112 - Date In and Date Complete to be marked as 09/02/2021
COB Feb 10nd – 125 Matched Trades from 179 - Date In and Date Complete to be marked as 10/02/2021
Tried my level best to give as much as possible information/requirement, can please let me know if required any further info
Thanks in advance for your time.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think there are various approaches possible.
Quite obvious would be to use interrecord-functions like previous() or peek() to look within other records. It requires a sorted resident-load and maybe more as one load because you need a logic to pick the right record for detecting/comparing the values. If the record could be anywhere you may need the mentioned extra pre-loads to create appropriate help-columns which are later used to determine the right record. That you didn't get directly extra records with interrecord-functions doesn't mandatory mean that such an method isn't applicable because you could apply your information in extra fields or concatenating them within a string which are later transformed with a crosstable/subfield-load in multiple records again.
Another approach would be to apply the logic already within your file-list loop. For it you may also need n pre-loads and some renaming of the key-field which would be the combination of the ID + DAY + ??? as KEY and with exists() you checked if the key exists or not.
Logically simpler is the following:
m: mapping load ID & '|' DAY, 'unmatched' resident t0;
t1: load distinct ID resident t0; join(t1) load distinct DAY resident t0;
t2: load *, applymap('m', ID & '|' DAY, 'matched') as STATE resident t1;
It creates a cartesian product which could have some benefits /alle possible data-combinations exists and real NULL analysis is possible) but also some disadvantages because the dataset could become quite huge and therefore too heavy for for your environment. But you don't need to keep it in this way because afterwards you could drop unneeded records whereby with some additionally logics you may already restrict the data in beforehand.
- Marcus
