Append Data to Resident Table

[joey_lutes]

I'm loading data from 24 spreadsheets.  The format is identical.

I have a successful load going for 1, but am trying to add #2.  I wish to append data to the existing resident Qlik Sense tables.

Specifically, this data is being loaded through a crosstable.  I think the long-term solution to this is a For . . While loop (which I'm not 100% sure how to do, but will tackle that in a bit) but for now, I'm just copying the load statements to see if it will successfully load 2 of them.

Here's what code looks like today (that's not working).  It successfully loads the first file, but the 2nd doesn't seem to appear.

Thoughts??

Forecasttemp:

Crosstable (ForecastDate, ForecastUtilization, 2)

Load *

FROM [lib://source/newfile1.xlsx]

(ooxml, embedded labels, table is [Forecast]);

ForecastData:

noconcatenate

  Load

  AutoNumber(Port) as PortKey,

        Date(Date#([Report Date], 'M/D/YYYY')) as ForecastReportDate,

        ForecastUtilization,

        Date(Num#(ForecastDate, 'M/D/YYYY')) as ForecastDate

Resident Forecasttemp;

drop table Forecasttemp;

Forecasttemp2:

Crosstable (ForecastDate, ForecastUtilization, 2)

Load *

FROM [lib://source/newfile2.xlsx]

(ooxml, embedded labels, table is [Forecast]);

ForecastData:

  Concatenate //(have tried with and without this)

    Load

  AutoNumber(Port) as PortKey,

        Date(Date#([Report Date], 'M/D/YYYY')) as ForecastReportDate,

        ForecastUtilization,

        Date(Num#(ForecastDate, 'M/D/YYYY')) as ForecastDate

Resident Forecasttemp2;

drop table Forecasttemp2;

[sunny_talwar · Accepted solution]

Have you tried something like this:

Forecasttemp:

Crosstable (ForecastDate, ForecastUtilization, 2)

Load *

FROM [lib://source/newfile*.xlsx]

(ooxml, embedded labels, table is [Forecast]);

ForecastData:

LOAD  AutoNumber(Port) as PortKey,

        Date(Date#([Report Date], 'M/D/YYYY')) as ForecastReportDate,

        ForecastUtilization,

        Date(Num#(ForecastDate, 'M/D/YYYY')) as ForecastDate

Resident Forecasttemp;

DROP Table Forecasttemp;

[joey_lutes]

Thanks, Sunny.

That certainly makes sense, and during the load I see it load it twice, but I have data with 2 different report dates which render as 'ForecastReportDate' and only one continues to show up (the first date).  It's the same problem I've been having.

No synthetic keys, no errors.

Any other thoughts?

[joey_lutes]

It is working.  Again - still not seeing that one value listed, but there are other values that are showing up from the 2nd file so that is the correct answer.  I'll try to figure out what's wrong with the rest of the data.

Thanks again.

[sunny_talwar]

Did it started working now? Or are you still trying to get this resolved?:)

[sunny_talwar]

May be the format of Report date differ between the two Excel files? Is that a possibility?

[joey_lutes]

It's all working - thank you.  The dates I had were in different formats in excel.  Have NO idea why, but I converted them all to text and now it works.

Thanks again - I'll continue to mess with the 'why' aspect.