Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
joey_lutes
Not applicable

Append Data to Resident Table

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;

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Append Data to Resident Table

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;

6 Replies
sunny_talwar
Not applicable

Re: Append Data to Resident Table

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
Not applicable

Re: Append Data to Resident Table

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
Not applicable

Re: Append Data to Resident Table

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
Not applicable

Re: Append Data to Resident Table

Did it started working now? Or are you still trying to get this resolved?Smiley Happy

sunny_talwar
Not applicable

Re: Append Data to Resident Table

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

joey_lutes
Not applicable

Re: Append Data to Resident Table

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.