conditional import

[AmCh]

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:

a

2

3

5

a

8

2

a

0

2

5

7

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

[QFabian]

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);

[Saravanan_Desingh]

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];

[Saravanan_Desingh]

Output:

[AmCh]

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.

[AmCh]

Hi QFabian,

it works perfectly too.

Thank you for your help!

[Saravanan_Desingh]

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

[AmCh]

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. 

[Saravanan_Desingh · Accepted solution]

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];

[Saravanan_Desingh · Accepted solution]

Output: