topic Duplicate records in QlikView

Duplicate records

Mon, 25 May 2009 21:00:47 GMT

Hi all,

I have a problem.

I must read, by a script, an external file (Excel) like:

FIELD1 FIELD2 FIELD3

A X 3

B Y 41

C Z 321

and I want to create a table QLV like:

FIELD1 FIELD2 FIELD3

A X 3

B Y 4

B Y 1

C Z 3

C Z 2

C Z 1

How can I to make for this problem?

Thank 1000 all.

Duplicate records

Anonymous — Mon, 25 May 2009 22:03:17 GMT

Not sure if this will work, but the first thing I'd try is:


LOAD
 FIELD1,
 FIELD2,
 subfield(FIELD3, '') as FIELD3
RESIDENT Table;

Duplicate records

Mon, 25 May 2009 22:19:29 GMT

Thank Michael,

But there is a problem.

If I execute the sample attached, it create toot much rows (See table Tab3).

Why?

Table1:
LOAD * INLINE [
Field1, Field2, F31, F32, F33, F34
A, X, 3, 4, 21, 3
B, Y, 41, 2, 1, 2
C, Z, 321, 3, 1, 42
];

Table11:
Load RecNo() as Indiv, *
resident Table1;

Tab2:
CROSSTABLE(CodQuestion,CodDettQuest0,3)
LOAD Indiv, Field1, Field2,
F31, F32, F33, F34
resident Table11;
Drop table Table1; Drop table Table11;

Tab3:
Load *,
subfield(CodDettQuest0,'') as CodDettQuest1
resident Tab2;
Rem Drop table Tab2;

Duplicate records

Tue, 26 May 2009 03:05:29 GMT

I would try a 'while' expression:

load

BR
Hans

F1
,F2
,mid(F3,Iterno(),1) as F3
resident table
while Iterno()<=len(F3) ;

Duplicate records

Tue, 26 May 2009 03:06:30 GMT

load

F1
,F2
,mid(F3,Iterno(),1) as F3
resident table
while Iterno()<=len(F3) ;

Duplicate records

Anonymous — Tue, 26 May 2009 23:51:10 GMT

OK, the seconf thing I"d try is this


Table1:
LOAD * INLINE [
 Field1, Field2, F31, F32, F33, F34
 A, X, 3, 4, 21, 3
 B, Y, 41, 2, 1, 2
 C, Z, 321, 3, 1, 42
];
Table11:
Load RecNo() as Indiv, *
resident Table1;
Tab2:
CROSSTABLE(CodQuestion,CodDettQuest0,3)
LOAD Indiv, Field1, Field2,
 F31, F32, F33, F34
resident Table11;
Drop table Table1; Drop table Table11;

for i=1 to 10 // assumong 10 is max possible length
Tab3:
Load
 *,
 mid(CodDettQuest0,$(i),1) as CodDettQuest1
resident Tab2
where len( mid(CodDettQuest0,$(i),1))>0;
next
Drop table Tab2;