Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to identify childs and create groups for the given data

Hi All,

I am looking for a solution for the sample data which i am posting here.

Please find the attachment for the data and requirements.

Below is the screenshot for the same.(Go through the attachment for complete details)

Screen.png

Thanks in advance.

Ravikumar

4 Replies
swuehl
MVP
MVP

I haven't fully understood your description, aren't 'T' items missing in your M1 material list?

Looks like you are coping with a kind of BOM, so maybe have a look at

Bill of Materials

Not applicable
Author

That is possible,

It is not mandatory all the sub items are required to make Final item that is why no T items in M1 list.

A final item is a contribution of sub items (May or may not with M items or may or may not with T items or may or may not with D and W items).

Thanks..

HirisH_V7
Master
Master

Hi,

Look into this,

Temp:

LOAD

IF(Left(Item,1) ='M',Item) as ItemGrouping,

Item,

Qty

FROM

[Item_Data_Sample Grouping-233096.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Store Temp into Data.qvd;

Drop Table Temp;

QVD:

LOAD @1 as ItemGrouping,

     @2 as Item,

     @3 as Qty,

     IF(@2=@1,If(@2=@1 and @3>=1,0,1)) as CreateFlag

FROM

[Data.qvd]

(qvd, filters(

Replace(1, top, StrCnd(null)),

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 1),

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 1),

Replace(4, top, StrCnd(null)),

Replace(1, top, StrCnd(null))

));

Data:

Load *,

  if(isnull(CreateFlag),Peek('Flag'),CreateFlag) as Flag

  Resident QVD;

  Drop Table QVD;

Using above Script and by creating a pivot table at front -end,

Item_Data_Sample Grouping-233096.PNG

HTH,

PFA,

Hirish

HirisH
MarcoWedel

Hi,

maybe one solution might be also:

QlikCommunity_Thread_233096_Pic1.JPG

QlikCommunity_Thread_233096_Pic2.JPG

mapLevel:

Mapping

LOAD Letter, RecNo() INLINE [

    Letter

    F

    M

    T

    A

    D

    W

];

table1:

LOAD *,

    If(Level>1,RangeSum(Peek(Group),-(Item like 'M*'))) as Group,

    If(Level>1,If(Item like 'M*',-(Qty=0),Peek(Flag))) as Flag,

    If(Level>Previous(Level),Peek(Path)&Repeat('/',Level-Previous(Level))&Item,If(Level=1,Item,Left(Peek(Path),Index(Peek(Path),'/',Level-1))&Item)) as Path;

LOAD RecNo() as ID,

    Item,

    ApplyMap('mapLevel',Left(Item,1)) as Level,

    Qty

FROM [https://community.qlik.com/servlet/JiveServlet/download/1125363-245898/Item_Data_Sample.xlsx] (ooxml, embedded labels, header is 1 lines, table is Sheet1);

hope this helps

regards

Marco