New datas based on existing

Wed, 22 Jul 2009 20:35:55 GMT

HI.

I'm newbie in Click.

I've got problem with creating new data based on existing.

I've got MS Excel file with just two columns.

KUNDE Segment code
133422 01000000000
163103 00000000000
133472 00000010010
160480 00000000000
170832 00000000000
162140 00000000000

This is file that asign Segmentation to Clients number. Segment code is code 01. Firstly i have to asign segment code to segment. This depend on position of 1 in code. Next i have to asign segment to upper segment. This is example script. I tried to do this for many way but it doesn't work. Please help me. Sorry for my English:)


Left join (Sales)
LOAD KUNDE as [Client number],
 [Segment code],
 if(left([Segment code],1)=1,'Mass client',(if(mid([Segment code],2,1)=1,'Good product',
 (if(mid([Segment code],3,1)=1,'Top shelf',
 (if(mid([Segment code],7,1)=1,'Small',(if(mid([Segment code],8,1)=1,'Medium',
 (if(mid([Segment code],9,1)=1,'Big',
 (if(mid([Segment code],10,1)=1,'Basic',(if(mid([Segment code],11,1)=1,'Luxury',
 (if(mid([Segment code],4,1)=1,'Key accounts',(if(mid([Segment code],5,1)=1,'Specialists',
 (if(mid([Segment code],6,1)=1,'Small Fire','None'))))))))))))))))))))) as Segment,

FROM [Additional data\Segmentacja.xls] (biff, embedded labels, table is Segmentacja$);
Next


Left join (Sales)
LOAD
 [Client number],
 [Segment code],
 [Segment],

 if (Segment='Mass client' or Segment='Good product' or Segment='Top shelf', Windows
 (if (Segment='Big' or Segment='Medium' or Segment='Small', Facade
 (if(Segment='Luxury' or Segment='Basic', Interiors
 (if(Segment='Key accounts' or Segment='Specialits' or Segment='Small Fire',Fire, None))))))) as [Upper Segment]
FROM Segmentacja$ (biff, embedded labels, table is Segmentacja1$);;

New datas based on existing

johnw — Thu, 23 Jul 2009 02:06:29 GMT

Maybe this?

pick(index("Segment code",'1')+1,'Other','Good product','Top shelf',...) as "Segment"
,pick(index("Segment code",'1')+1,'None' ,'Windows' ,'Windows' ,...) as "Upper Segment"

It might be too simplistic, as you aren't checking the positions in order like this will, but I don't know if that's important. If it is, rearranging the codes to the order you want might be simpler than a series of IF() statements.

As for the posted script, I'm not sure what the "Next" is doing there since you don't otherwise show a loop, you have two semicolons after the last statement, you need to put literals like 'Windows' in single quotes in the second load, and there may be other problems because I didn't look closely.

topic New datas based on existing in QlikView

New datas based on existing

New datas based on existing