topic Re: conditional import in QlikView

conditional import

AmCh — Wed, 10 Feb 2021 16:06:07 GMT

Hi all,

given the following xls. table as an example:

x	a	b	c	d
1	2	1	5	2
2	3	4	5
3	5	7	1
1	8	3	7	5
2	2	6	5
1	0	9	3	8
2	2	8	1
3	5	5	5
4	7	2	6

1. I want to import the fields a,b,c separately according to the values of the field x:

Always import separately from X=1 to the next X=1 (excluded).

As an example:

same for b and c.

2. For d I only want to import single values that correspond to X=1.

//FROM [alpha] (ooxml, embedded labels, header is 1 lines, table is beta)

Any help please?
Thanks in advance

Re: conditional import

QFabian — Wed, 10 Feb 2021 18:34:09 GMT

Hi @AmCh , please try this, is going to add a new field 'x2', and then you can use it to separate your data.

LOAD
recno(),
x,
if(rowno()=1, 1, if(x = 1, peek(x2)+1, peek(x2))) as x2,
a,
b,
c,
d
FROM
[..\Documents\sepataed.xlsx]
(ooxml, embedded labels, table is Hoja1);

Re: conditional import

Saravanan_Desingh — Thu, 11 Feb 2021 01:49:17 GMT

One solution is,

tab1: LOAD RowNo() As RowID,*, If(x=1, RangeSum(Peek(K1),1), Peek(K1)) As K1; LOAD * INLINE [ x, a, b, c, d 1, 2, 1, 5, 2 2, 3, 4, 5, 3, 5, 7, 1, 1, 8, 3, 7, 5 2, 2, 6, 5, 1, 0, 9, 3, 8 2, 2, 8, 1, 3, 5, 5, 5, 4, 7, 2, 6, ]; Generic LOAD 1, 'a'&K1, a Resident tab1; Generic LOAD 1, 'b'&K1, b Resident tab1; Generic LOAD 1, 'c'&K1, c Resident tab1; Generic LOAD 1, 'd'&K1, d Resident tab1; Drop Field [1];

Re: conditional import

Saravanan_Desingh — Thu, 11 Feb 2021 01:50:04 GMT

Output:

Re: conditional import

AmCh — Thu, 11 Feb 2021 11:46:25 GMT

Hi saran7de,

Thank you very much for your quick reply. It works perfectly.

Instead of naming a1 ... d3, how about naming the fields with the d field values?

For example: a2, a5 and a8?

Thanks in advance.

Re: conditional import

AmCh — Thu, 11 Feb 2021 11:47:10 GMT

Hi QFabian,

it works perfectly too.

Thank you for your help!

Re: conditional import

Saravanan_Desingh — Fri, 12 Feb 2021 00:31:35 GMT

So, u want a, b & c data with d as Label? Sorry I didn't understand.

Re: conditional import

AmCh — Fri, 12 Feb 2021 09:46:19 GMT

The separately loaded fields a'1', a'2' and a'3' as a'2' a'5' and a'8' (since d1=2, d2=5 and d3=8).

Also values of d as index of a,b & c.

Re: conditional import

Saravanan_Desingh — Fri, 12 Feb 2021 14:33:32 GMT

Try this,

tab1: LOAD RowNo() As RowID,x, a, b, c, If(Len(d)=0, Peek('d'), d) As d; LOAD * INLINE [ x, a, b, c, d 1, 2, 1, 5, 2 2, 3, 4, 5, 3, 5, 7, 1, 1, 8, 3, 7, 5 2, 2, 6, 5, 1, 0, 9, 3, 8 2, 2, 8, 1, 3, 5, 5, 5, 4, 7, 2, 6, ]; Generic LOAD 1, 'a'&d, a Resident tab1; Generic LOAD 1, 'b'&d, b Resident tab1; Generic LOAD 1, 'c'&d, c Resident tab1; Drop Field [1];

Re: conditional import

Saravanan_Desingh — Fri, 12 Feb 2021 14:33:57 GMT

Output:

Re: conditional import

QFabian — Fri, 12 Feb 2021 15:32:21 GMT

😊

Re: conditional import

AmCh — Fri, 12 Feb 2021 15:36:01 GMT

Nice, it works!
Thank you for your help.