Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Generic Load with new headers

Hello,

First of all I apologize if this has been anwered before, but I've spent the morning looking without any success..

I think what I'm trying to do is a generic load but I can't get it to work with the headers I need.

My data is the following;

ItemUnitNo In UnitBalancePriceXXX
1A110010XXX
1B1010010XXX
1C1001008XXX
2A120020XXX
2D5020020XXX
3E15030XXX
4B11040XXX

So basically every "Item" can have up to three units. The units can be different. "No in unit" describes how many of the unit is in the first entry of the item (Example; on Item 1, there's 100 "Unit" A in a "Unit" C).

What I'd like to do is a table (doesn't matter if I have to change in script or via Set Analysis) that is displayed like below.

ItemPrimary UnitPrimary PriceSecondary UnitNo in SecondarySecondary PriceTertiary UnitNo in TertiaryTertiary PriceBalance
1A10B1010C1008100
2A20D5020---200
3E30------50
4A40------10

 

So I need to create new headers and place them in secondary & tertiary based on their size in "No in unit". The primary unit always has 1 "No in unit". Currently I have a straight up simple load in one table.

Thank you so much for taking your time to read my question.

1 Reply
Highlighted
Partner
Partner

Hi @Joel_Holmberg 

The example below might help you in finding a solution to your problem.

As you see in the picture the output looks like your desired output.

image.png

 

 

TmpOrderLabel:
LOAD dual(A,B) as Order
Inline [
A, B
Primary, 1
Secondary, 2
Tertiary, 3
];
Data:
LOAD
if(peek('Item')=Item, Peek('Order')+1, 1) as Order,
 * inline [ 
Item	Unit	No In Unit	Balance	Price	X	XX	XXX
1	A	1	100	10	X	X	X
1	B	10	100	10	X	X	X
1	C	100	100	8	X	X	X
2	A	1	200	20	X	X	X
2	D	50	200	20	X	X	X
3	E	1	50	30	X	X	X
4	B	1	10	40	X	X	X
] (delimiter is '	');

DROP TABLE TmpOrderLabel;
Base:
LOAD 
	Item, 
	only(Balance) as Balance,
	Only(X) as X,
	Only(XX) as XX,
	Only(XXX) as XXX
Resident Data
group by Item
;

	GL:
	Generic LOAD 
		Item, Order, Unit& '|'& Price & '|' & [No In Unit] as Value
	Resident Data;

Drop Table Data;



	CombinedTable:
   	Load distinct Item Resident Base;

 
   For each vTableName in 'GL.Primary','GL.Secondary','GL.Tertiary'

      Left Join (CombinedTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName
 
 LEFT JOIN (Base) LOAD
	Item,
  	SubField(Primary, '|',1 ) as [Primary Unit],
  	SubField(Primary, '|',2 ) as [Primary Price],  	
  	SubField(Primary, '|',3 ) as [No In Primary],
  	SubField(Secondary, '|',1 ) as [Secondary Unit],
  	SubField(Secondary, '|',2 ) as [Secondary Price],
  	SubField(Secondary, '|',3 ) as [No In Secondary],
  	SubField(Tertiary, '|',1 ) as [Tertiary Unit],
  	SubField(Tertiary, '|',2 ) as [Tertiary Price],
  	SubField(Tertiary, '|',3 ) as [No In Tertiary]
  Resident
  	CombinedTable
   ;
   Drop table CombinedTable
   

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes