topic Re: Append Data to Resident Table in App Development

Append Data to Resident Table

joey_lutes — Thu, 25 Aug 2016 16:53:44 GMT

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;

Re: Append Data to Resident Table

sunny_talwar — Thu, 25 Aug 2016 20:46:47 GMT

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;

Re: Append Data to Resident Table

joey_lutes — Thu, 25 Aug 2016 21:45:38 GMT

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?

Re: Append Data to Resident Table

joey_lutes — Thu, 25 Aug 2016 23:55:46 GMT

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.

Re: Append Data to Resident Table

sunny_talwar — Fri, 26 Aug 2016 00:10:56 GMT

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

Re: Append Data to Resident Table

sunny_talwar — Fri, 26 Aug 2016 08:31:07 GMT

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

Re: Append Data to Resident Table

joey_lutes — Mon, 29 Aug 2016 22:54:59 GMT

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.