Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a script that uses txt data files as input.
The table fields are mapped in the script based on the position of each character in the text files. Here's an excerpt:
Folder_Doc_list:
LOAD distinct
[@ 2: 15] as [Favored Doc],
[@ 2: 15] the [NE Initial - Favored], //// key
if ([@ 16:16] = 1, 'PJ',
if ([16:16] = 2, 'PF',
if [[16:16] = 3, 'IG', 'UG'))) as [Favored Type Doc],
if ([@ 16:16] = 1, 'Legal entity',
if ([@ 16:16] = 2, 'Individual'
if ([@ 16:16] = 3, 'Generic Registration',
'Unidade Gestora'))) as [Favored Name]
[@ 17: 35] as [Name Reduced Favored Doc],
[@ 36: 90] as [Favored Name Doc],
[@ 2: 15] & '-' & [@ 36:90] as [Favored Complete Doc],
[@ 91: 155] as [Address Favored Doc],
[@ 156: 159] as [Cod. Municipality Favored Doc],
[@ 160: 167] as [CEP Favored Doc],
[@ 168: 169] as [UF Favored Doc],
[@ 170: 172] as [Cod. Favored Country Doc],
[@ 173: 217] as [Phone Favored Doc],
[@ 218: 262] as [End. Electronic Favored Doc],
[@ 263: 265] as [Cod. Legal Nature Favored Doc],
FROM
[..\1. Data - originals \ Creditor \ * .TXT]
(fix, codepage is 1252);
Some joins are made to add other fields from other tables (also created from text files), and in the end, the qlik script generates an output text file that serves as input for an Access application. This file have unique records as I used distinct clause, but there are records that are very similar being stored at the output text file, which makes the access application work bad and mix up things. Here is an example:
I need to make the script read the text files and store in the output file only the first occurrence of the record based on its key, ignoring any other records with the same key value. I've tried using FirstValue but not successfully. Any ideas?
Can you try with Min / Max script functions
TableNew:
Load
Min([@ 2: 15] the [NE Initial - Favored]) AS Key,
*
RESIDENT Folder_Doc_list
Group by [@ 2: 15] the [NE Initial - Favored]
You could try something like this:
Folder_Doc_list:
load '' as [NE Initial - Favored] autogenerate 0;
concatenate(Folder_Doc_list)
LOAD distinct
[@ 2: 15] as [Favored Doc],
[@ 2: 15] the [NE Initial - Favored], //// key
if ([@ 16:16] = 1, 'PJ',
if ([16:16] = 2, 'PF',
if [[16:16] = 3, 'IG', 'UG'))) as [Favored Type Doc],
if ([@ 16:16] = 1, 'Legal entity',
if ([@ 16:16] = 2, 'Individual'
if ([@ 16:16] = 3, 'Generic Registration',
'Unidade Gestora'))) as [Favored Name]
[@ 17: 35] as [Name Reduced Favored Doc],
[@ 36: 90] as [Favored Name Doc],
[@ 2: 15] & '-' & [@ 36:90] as [Favored Complete Doc],
[@ 91: 155] as [Address Favored Doc],
[@ 156: 159] as [Cod. Municipality Favored Doc],
[@ 160: 167] as [CEP Favored Doc],
[@ 168: 169] as [UF Favored Doc],
[@ 170: 172] as [Cod. Favored Country Doc],
[@ 173: 217] as [Phone Favored Doc],
[@ 218: 262] as [End. Electronic Favored Doc],
[@ 263: 265] as [Cod. Legal Nature Favored Doc],
FROM
[..\1. Data - originals \ Creditor \ * .TXT]
(fix, codepage is 1252) where not exists([NE Initial - Favored], [@ 2: 15]);
- Marcus