allocation of given ids to set of records

[askarkhan]

Hi, 

i have two files, one file is head count and other is raw dump of customers. i want to assign the head count emp_ids evenly to raw dump records on basis of store name. I am attaching head count, raw dump and final result(expected) here. please help to automate in qlikview. tried using variable. but haven't succeeded.

QlikView 

Thanks in advance.

[avinashelite · Accepted solution]

Please find the solution below

Head_count:
LOAD If([STORE NAME]=Previous([STORE NAME]),Peek(Count)+1,1) as Count,
[STORE NAME],
EMP_ID
FROM
[C:\Users\Downloads\HEAD COUNT.xlsx]
(ooxml, embedded labels, table is Sheet1);

Count_Map:
Mapping LOAD
[STORE NAME],
max(Count) as Number_Of_Employee
Resident
Head_count
Group by
[STORE NAME];

Final_Table:
LOAD *,
[STORE NAME]&'_'&EMP_ASS as Assignment_Key;
Load *,
if(Mod(Cust_count,ApplyMap('Count_Map',[STORE NAME]))=0,ApplyMap('Count_Map',[STORE NAME]),Mod(Cust_count,ApplyMap('Count_Map',[STORE NAME]))) as EMP_ASS;

LOAD If([STORE NAME]=Previous([STORE NAME]),Peek(Cust_count)+1,1) as Cust_count,
[Customer Name],
[STORE NAME],
[Last purchased amount],
[Total Sale]

FROM
[C:\Users\Downloads\RAW DUMP.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
Left join(Final_Table)
LOAD
[STORE NAME]&'_'&Count as Assignment_Key,
EMP_ID
Resident
Head_count;

Drop Table Head_count;

[askarkhan]

Thanks Man. it works

[avinashelite]

Always there to help, happy Qlik!!