how co convert 010001 to Tu,Su

[omnomnom]

Hello!

i have a colomn with accepted days data like

ORDERDAYS

0010000

1000000

1100000

1010000

and so on

how to convert it using string functions to

ORDERDAYS

WE,

MO,

MO,TU

MO,WE

and so on

now i harcoded it like this

= if( right(left('1110000',1),1)=1,'Mo')&

if( right(left('1110000',2),1)=1,'Tu')&

if( right(left('1110000',3),1)=1,'We')&

if( right(left('1110000',4),1)=1,'Th')&

if( right(left('1110000',5),1)=1,'Fr')&

if( right(left('1110000',6),1)=1,'Sa')&

if( right(left('1110000',7),1)=1,'Su')

but i think there are more clear way to do it?

[marcus_sommer]

For such kind of data-matching is mapping with applymap() well suited, see here what mapping is:

Mapping … and not the geographical kind

- Marcus

[omnomnom]

Hi! im afraid there is no clear way to do it with apply map cause

you need to store apply table with all  combinations of

ORDERDAYS.

[marcus_sommer]

What do you mean with all combinations? In your example there are 7 different values which are connected to a weekday - or is there are another logic?

- Marcus

[omnomnom]

Colomn ORDERDAYS contains

about 7000 values with different combinations of 0 and 1

i write an example with first 4 values

[marcus_sommer]

How do you identify which binary number is which weekday? Are there other informations included in this? Further exists another fields with dates linked to them?

- Marcus

[omnomnom]

1010000

it can be easy identified by place number in string

1 - 1 0 - 2 1 - 3 0 - 4 0 - 5 0 - 6 0 - 7

colomn ORDERDAYS always contains 7 numbers

[antoniotiman]

Hi,

I think Your approach is correct.

However use Mid() function

If(Mid(ORDERDAYS,1,1) = '1',',MO')&If(Mid(ORDERDAYS,2,1)='1',',TU') and so on

You remove the first comma from the result

If(Left(Result,1)=',','')

Regards,

Antonio

[alexandros17]

I think you could use other string functions but you have to perform at least 7 comparison to obtain your result, so keep your formula because it is simply and it works ... you can compute it in the script to simplify expressions.

[marcus_sommer · Accepted solution]

Here an approach which worked with at least one additionally load - but I think it could be more easier if you could translate and match these binary numbers with normal numbers maybe per bit shifting - but I'm not a first/second level programmer ...

table:

Load rowno() as Number, num(rowno(), '(bin)') as Binary AutoGenerate 150;

table2:

Load

    Binary, rowno() as RowNo, iterno() as IterNo,

    pick(match(mid(Binary, iterno(), 1) * iterno(), 1,2,3,4,5,6,7), 'Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa', 'Su') as WeekDay

Resident table while iterno() < 8;

table3:

Load Binary, concat(WeekDay, ',', IterNo) as WeekDayConcat Resident table2 Group by Binary;

- Marcus