Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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]

Highlighted
MVP & Luminary
MVP & Luminary

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