topic How to identify childs and create groups for the given data in QlikView

How to identify childs and create groups for the given data

Sat, 17 Sep 2016 08:26:21 GMT

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)

Thanks in advance.

Ravikumar

Re: How to identify childs and create groups for the given data

swuehl — Sat, 17 Sep 2016 08:38:10 GMT

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

Re: How to identify childs and create groups for the given data

Sat, 17 Sep 2016 08:54:53 GMT

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..

Re: How to identify childs and create groups for the given data

HirisH_V7 — Sat, 17 Sep 2016 12:16:21 GMT

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,

HTH,

PFA,

Hirish

Re: How to identify childs and create groups for the given data

MarcoWedel — Mon, 19 Sep 2016 00:06:31 GMT

Hi,

maybe one solution might be also:

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