topic Re: Join Against master table - Data manipulation in App Development

Join Against master table - Data manipulation

sanket94 — Sat, 16 Nov 2024 03:40:53 GMT

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:

Re: Join Against master table - Data manipulation

dwforest — Fri, 03 Jan 2020 20:05:00 GMT

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);

Re: Join Against master table - Data manipulation

sanket94 — Fri, 03 Jan 2020 20:31:36 GMT

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.

Re: Join Against master table - Data manipulation

Kushal_Chawda — Fri, 03 Jan 2020 20:52:40 GMT

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;

Re: Join Against master table - Data manipulation

sanket94 — Fri, 03 Jan 2020 21:00:10 GMT

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

Re: Join Against master table - Data manipulation

Kushal_Chawda — Fri, 03 Jan 2020 21:35:18 GMT

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