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: 
mvgrl1976
Contributor
Contributor

How to avoid storing similar records with commom key

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:


example.PNG


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?

2 Replies
nsetty
Partner - Creator II
Partner - Creator II

Can you try with Min / Max script functions

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/AggregationFunctions/min...

TableNew:

Load

Min([@ 2: 15] the [NE Initial - Favored]) AS Key,

*

RESIDENT Folder_Doc_list

Group by [@ 2: 15] the [NE Initial - Favored]

marcus_sommer

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