Discussion Board for collaboration on QlikView Scripting.
i've a table like this
I would like to obtain a final table like this:
I don't be so good qlikview developer.
I suppose that I've to use before a CrossTable and then a Subfield but i'm not able.
Once I get the table represented, I can use it for an IntervalMatch function.
In this table I've the minimum (From) and maximum (To) value.
Anyone could help me?
Please I'm in difficulty.
Bye and thanks
Go to Solution.
LOAD ID, Des, RG1, date#(right(subfield(RG2, ';'),8),'YYYYMMDD') as From INLINE [
ID, Des, RG1, RG2
ITEM1, Item used 66% time, Gr1, 1-01-19000101;2-01-20130615
ITEM2, Item used 15% time, Gr1, 1-01-19000101
ITEM3, Item never used, Gr1
ID, Des, RG1, From,
if(previous(ID)=ID, previous(From)) as To
order by ID, From desc ;
drop table Input;
See attached example.
Thanks, the answer is correct.
Now I have to get the two columns MC and Tree but I hope to be able.
Thank you very much
I did't think you could do it in so little time.
Ok, I obtained the desired result.
Thank you very much, I write the example with all the field managed.
LOAD ID, Des, RG1, subfield(RG2,';') as FIELD1
ITEM4, ADRIANO, GR_AF, 5-01-19000101;2-01-20130701;3-03-20140115
ITEM5, ADRY, GR_AA, 1-02-19000101;2-02-20130701;3-02-20140115;4-02-20140320
ITEM6, ADRY, GR_AA, 4-01-19000101;3-02-20130701;2-03-20140115;1-04-20140515
ITEM7, ADRY_X, GR_AX, 1-01-19000101;3-02-20130701;2-03-20140115;5-04-20140620
ID, Des, RG1,
if(previous(ID)=ID, previous(FIELD1)) as FIELD2
order by ID, FIELD1 desc ;
LOAD ID, Des, RG1
, left(FIELD1,1) as MC
, mid(FIELD1,3,2) as Tree
, date#(right(FIELD1,8),'YYYYMMDD') as From
, if(len(Right(FIELD2,8))>0,date(date#(Right(FIELD2,8),'YYYYMMDD')-1,'YYYYMMDD'),'22000101') as To
drop table Result;