Join Against master table - Data manipulation

[sanket94]

Hi All,

  I am new to the Qliksense data modeling part.  I am looking for help in regards to Joining multiple files to the master file.

File 1: ( We would receive these files daily)

I have a file received daily with FilenameDate (eg.ACT12-12-2019, ACT12-13-2019) 

It has the following columns:

Store

IN Store Dt and Time

Sum of Shipped Cartons

Key ( Concat of Instoredt and Store)

File2 ( Master File)

Store No 

Cartons ( Manually kept as 0)

Now consider that I have 500 stores of which the vity report has 230 stores with estimated cartons. I want to Join this data of file one to file 2 so that remaining stores 270 could be added to the file with DC shipped cartons as 0 . also need to have the Instore date.

Please consider that activity chart could have 2 lines of same-store with different cartons shipped 

Example: 

[dwforest]

Put in the necessary connections to your filename and [Master File]

vInStoreDate = Date(Mid(filename,3,10),"MM-DD-YYYY")

LOAD Store, Cartons,  $(vInstoreDate) as InStoreDt

FROM filename;

LOAD [Master fie],0, $(vInstoreDate)

FROM [Master File] 

WHERE NOT Exists(Store,Store);

[sanket94]

I tried working with the above code but did not work for me. I have attached the files.

Please note it would be great we could bounce each activity file to the master file so that we could have 0 cartons for those respective stores. and save each file with an original filename as of activity file

Please find the attached file 

The activity file is daily received files and store0 is Masterfile.

[Kushal_Chawda]

I am not sure why you want to join master file with individual activity file. You can simply lookup master file with all activity file once and update the field like below

Map:
mapping Load  Store,
      [DC Shipped]
FROM MasterFile;

Activity:
Load *,
     applymap('Map',Store,[DC Shipped]) as [DC Shipped Final]
FROM ActivityInquiry_*.qvd;

[sanket94]

Basically I am trying to compare estimated vs actual cartons received based on daily files.

 So I want to add 0 cartons estimated to daily file and then when I receive delivery on that day. we could know there is a discrepancy. Example.  expected cartons could be 0 but received 10 or vice versa

I am trying to add cartons as  0 as an activity file would only have instances of store when they are expecting any delivery

[Kushal_Chawda]

I think what I suggested should work if not please provide example with the sample provided